Archived ticket

[Mopi]

Good morning everyone !

I have tested OTRS solution (5s) and I can say features are great ! But something in the core disturbed me.

I would like to clarify how archives work. If I understood well, it only switches a binary value in the database to "1". I don't understand how it can be a gain of time. Indeed, archived records don't move to another table (right?) so since the SQL request will need to search for each record to know if the value archive_flag is set to "0" or "1" to display the ones with "0"...

If this works like this, what happens when you have like 2 millions of archived ticket? To show the 5000 opened on the screen, database will need to search in all the tickets one by one to know if the value is set to "0"? That seems crazy

I would like to know if I missed something who does this kind of work :
- exports tickets (or at least change the table for archived ticket to prevent a search in them each time)
- stores them in a good way (in another DB or table like archived_ticket)
- deletes the exported tickets from current DB or table

If nothing exists, how do people who would like to keep all old tickets ? Own scripts to export/restore/purge in a second database with the same schema?

I thank you in advance for your time,

Mopi

[RStraub]

You can always move archived tickets to the filesystem and out of the DB.
As for the performance, I greatly suspect that there's an index on the archive flag. With the index the DB will not - as you say - lookup each ticket and decide whether it matches a search.

[Mopi]

Thanks for the fast answer !

I didn't know I could move archived tickets to the filesystem ! Did I miss something? ^^'

I thought only attachements (article_attachement and article_plain) were stored in the filesystem, other tickets information stayed in the database and if needed, the content_path was used to get attachements stored in the filesystem.

If I am wrong, how to export archived tickets in the filesystem then? pls

I will have a look about this index, ty

For the performance, I would be interested to have feedbacks from users who kept all old / archived tickets.

Mopi

[RStraub]

I haven't tried it, but isn't that the setting "Ticket::ArchiveSystem" in SysConfig -> "Ticket → Core::Ticket" ?

As for the index, there is one, just check with:

Code: Select all

show indexes from ticket;

It's a btree over `ticket_archive_flag`

[Mopi]

No ! ^^ the setting "Ticket::ArchiveSystem" in SysConfig -> "Ticket → Core::Ticket" (as I saw) only put the ticket_archive_flag value to "1".

That's my point, and the reason why I created a topic, SQL requests will check each record because the request needs to know if the record has a "0" or a "1" in this field to display the good ones.

B-tree index is a standard / default index, used almost everywhere. I think this index is just there when you need to search in the archived tickets from the interface to reach them faster.

But I would like a real confirmation about this functionning and the performance issues.

Mopi