Skip to content
Snippets Groups Projects
  • Jared Hancock's avatar
    perf: Use a materialized view to speed queue views · 1bc05945
    Jared Hancock authored
    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.
    1bc05945