IPManager 2.2 tickets sql improvements.
Michael Dale
Primary tickets query in IPManager, working very nicely (110,000 tickets in a slow VM).
SQL result
Host: localhost
Database: ipm_test
Generation Time: Jun 21, 2011 at 01:10 AM
Generated by: phpMyAdmin 3.2.0.1 / MySQL 5.1.36-community-log
SQL query: EXPLAIN SELECT tickets.* , c.id AS `source_client_id`, c.client_name AS `source_client_name`, c.email AS `source_client_email`, c2.client_name AS `assigned_user_name`, c2.email AS `assigned_user_email`, comp.name AS `source_company_name`, p.name AS `priority_name`, t.name AS `ticket_state_name` FROM tickets LEFT JOIN clients c ON c.id = tickets.source_client_id LEFT JOIN clients c2 ON c2.id = tickets.assigned_user_id LEFT JOIN priorities p ON p.id = tickets.priority_id LEFT JOIN ticket_state t ON t.id = tickets.ticket_state_id LEFT JOIN companies comp ON comp.id = tickets.source_company_id WHERE 1 = 1 AND tickets.ticket_state_id = 1 ORDER BY last_modified DESC LIMIT 50 OFFSET 0;
Rows: 6
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | tickets | index | ticket_state_id | last_modified | 8 | NULL | 50 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | ipm_test.tickets.source_client_id | 1 | |
| 1 | SIMPLE | c2 | eq_ref | PRIMARY | PRIMARY | 4 | ipm_test.tickets.assigned_user_id | 1 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | ipm_test.tickets.priority_id | 1 | |
| 1 | SIMPLE | t | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | comp | eq_ref | PRIMARY | PRIMARY | 4 | ipm_test.tickets.source_company_id | 1 |