MySQL Performance: MySQL => Left Join Not using index (or how to index this query)?

Left Join Not using index (or how to index this query)? [message #1646]

Sat, 18 August 2007 20:44

bproven
Messages: 12
Registered: August 2007
Location: Fort Collins CO

Junior Member

I have the following query that is executing at a VERY slow rate. Usually takes about 6 seconds to return. I have tried several index strategies (to the best of my ability - which I admit is probably lacking) to no avail. I have copied the query below and the explain - any help in optimizing via an index(es) would be greatly appreciated.

Please be aware that this is a query produced by a boxed application (<cough> SugarCRM <cough>) and I have little control over the way its written (its kind of ugly) unless I dig though the PHP code. I wanted to try an index optimization first if possible.

SELECT  cases.id, cases_cstm.*, cases.case_number, cases.name, 
accounts.name account_name1, cases.account_id, 
cases.priority, cases.status, 
cases.date_entered , cases.modified_user_id, assigned_user0.user_name modified_user_id, 
assigned_user1.user_name assigned_user_name, accounts.assigned_user_id account_name1_owner, 'Accounts' account_name1_mod, 
cases.assigned_user_id  
FROM cases 
left JOIN cases_cstm ON cases.id = cases_cstm.id_c   
left JOIN accounts accounts ON accounts.id= cases.account_id 
AND accounts.deleted=0 AND accounts.deleted=0 
left JOIN users assigned_user0 ON assigned_user0.id=cases.modified_user_id   
left JOIN users assigned_user1 ON assigned_user1.id=cases.assigned_user_id   
where (1) AND cases.deleted=0 
ORDER BY cases.case_number ASC LIMIT 0,21;

The EXPLAIN:

 
+----+-------------+----------------+--------+-------------------------------------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
| id | select_type | table          | type   | possible_keys                                   | key     | key_len | ref                               | rows | Extra                                        |
+----+-------------+----------------+--------+-------------------------------------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | cases          | ALL    | NULL                                            | NULL    | NULL    | NULL                              | 1495 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | cases_cstm     | ALL    | NULL                                            | NULL    | NULL    | NULL                              | 1537 |                                              |
|  1 | SIMPLE      | accounts       | eq_ref | PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del | PRIMARY | 108     | infoathand.cases.account_id       |    1 |                                              |
|  1 | SIMPLE      | assigned_user0 | eq_ref | PRIMARY                                         | PRIMARY | 108     | infoathand.cases.modified_user_id |    1 |                                              |
|  1 | SIMPLE      | assigned_user1 | eq_ref | PRIMARY                                         | PRIMARY | 108     | infoathand.cases.assigned_user_id |    1 |                                              |
+----+-------------+----------------+--------+-------------------------------------------------+---------+---------+-----------------------------------+------+----------------------------------------------+

I cannot figure out how to get cases and case_cstm to use an index I setup. Strangely (or maybe not), if I change this query to use an INNER JOIN instead of a LEFT JOIN it executes in .5 sec instead of 6 secs with no change to the current indexes.

Anyhow, any help is appreciated. I can post show index statements if that helps to see the keys of each table. I appreciate any help - I have been banging my head against a wall for the last day to figure out the MySQL optimizer.

EDIT: jsut wanted to mention the MySQL version is 5.0.22 running on Ubuntu Dapper Server - using MyISAM

[Updated on: Sun, 19 August 2007 13:11]

Report message to a moderator