osTicket/osTicket-1.8 — pull request #357 by greezybacon (2013-12-26 18:47:44+00:00)
Title: perf: Use a materialized view to speed queue views

This patch introduces an automatic materialized view to speed database performance when querying and displaying the ticket views. This can eventually be extended to the search and advanced search features to speed them as well.

The data from the dynamic form entries related to ticket details is copied to a %ticket__cdata table. The %ticket__cdata table is then joined directly to the other tables in the query for the ticket view. MySQL is magically and dramatically faster using this method.

The downside is that the disk usage for the custom data is doubled, and the time needed to update the dynamic data is at least doubled as the form entries and the materialized view must both be updated.

This method should also extend well to other database platforms in the future. It will be likely that most other database query optimizers will have difficulty joining, scanning, and sorting the table models we have for custom data fields.