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