| 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]
|
|
|
| Re: Left Join Not using index (or how to index this query)? [message #1647 is a reply to message #1646 ] |
Sun, 19 August 2007 12:47  |
bproven
Messages: 12
Registered: August 2007
Location: Fort Collins CO |
Junior Member |
|
|
Another interesting twist - if I trim the above query down to one LEFT JOIN where the join is on 2 primary keys in the 2 tables cases and case_cstm, MySQL will NOT use the keys. Why?
SELECT cases.id , cases_cstm.*, cases.case_number ,
cases.name , cases.priority , cases.status ,
cases.date_entered , cases.modified_user_id,
cases.assigned_user_id
FROM cases
left JOIN cases_cstm ON cases.id = cases_cstm.id_c
where cases.deleted=0
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 |
| 1 | SIMPLE | cases_cstm | ALL | NULL | NULL | NULL | NULL | 1537 | |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
This query takes 5-6 seconds to complete. Change it to an INNER join (instead of LEFT) and its done in .07 sec. The INNER join EXPLAIN is below:
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | cases_cstm | ALL | NULL | NULL | NULL | NULL | 1537 | |
| 1 | SIMPLE | cases | eq_ref | PRIMARY | PRIMARY | 108 | func | 1 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
Uses the key/index in this one?!
I have a handful of queries like the one in the first post that are really beating the server into the ground. I would like to optimize using indexes (if possible). Problem is I can't guess what the optimizer will do.  Any pointers are welcome... thanks.
[Updated on: Sun, 19 August 2007 12:48]
|
|
|
|
|
|
|
|
|
| Re: Left Join Not using index (or how to index this query)? [message #1680 is a reply to message #1679 ] |
Thu, 23 August 2007 11:59 |
bproven
Messages: 12
Registered: August 2007
Location: Fort Collins CO |
Junior Member |
|
|
| chriswest wrote on Thu, 23 August 2007 11:31 |
Are you sure there is a primary key / index on cases_cstm.id_c?
how about a simple
EXPLAIN SELECT * FROM cases_cstm WHERE id_c = number
to figure this out.
|
Here is the EXPLAIN:
mysql> explain select * from cases_cstm where id_c = 'f211ee71-2d3f-9db0-99d1-45e448a63c99';
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | cases_cstm | const | PRIMARY | PRIMARY | 36 | const | 1 | |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.03 sec)
Below are the table schema for cases and cases_cstm (SHOW CREATE TABLE results):
Cases table:
CREATE TABLE `cases` (
`id` char(36) NOT NULL,
`case_number` int(11) NOT NULL auto_increment,
`date_entered` datetime NOT NULL,
`date_modified` datetime NOT NULL,
`modified_user_id` char(36) NOT NULL,
`assigned_user_id` char(36) default NULL,
`created_by` char(36) default NULL,
`effort_actual` double default NULL,
`effort_actual_unit` varchar(20) default NULL,
`travel_time` double default NULL,
`travel_time_unit` varchar(20) default NULL,
`arrival_time` varchar(30) default NULL,
`cust_req_no` varchar(30) default NULL,
`cust_contact_id` char(36) default NULL,
`cust_phone_no` varchar(30) default NULL,
`date_closed` date default NULL,
`date_billed` date default NULL,
`vendor_rma_no` varchar(30) default NULL,
`vendor_svcreq_no` varchar(30) default NULL,
`contract_id` char(36) default NULL,
`asset_id` char(36) default NULL,
`asset_serial_no` varchar(100) default NULL,
`category` varchar(40) default NULL,
`type` varchar(40) default NULL,
`deleted` tinyint(1) NOT NULL default '0',
`name` varchar(255) default NULL,
`account_name` varchar(100) default NULL,
`account_id` char(36) default NULL,
`status` varchar(25) default NULL,
`priority` varchar(25) default NULL,
`description` text,
`resolution` text,
PRIMARY KEY (`id`),
KEY `case_number` (`case_number`),
KEY `idx_case_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
Cases_cstm:
CREATE TABLE `cases_cstm` (
`id_c` char(36) NOT NULL,
`mcs_steps_to_reproduce_c` text,
`mcs_applications_multi_c` text NOT NULL,
`mcs_supportcase_source_c` varchar(150) default NULL,
`mcs_legacy_tt_number_c` int(11) default NULL,
PRIMARY KEY (`id_c`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
Also the SHOW INDEX from cases_cstm for completeness (the SHOW INDEX for cases is in the previous post):
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| cases_cstm | 0 | PRIMARY | 1 | id_c | A | 3136 | NULL | NULL | | BTREE | NULL |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
|
|
|
|
|
|
| Re: Left Join Not using index (or how to index this query)? [message #1684 is a reply to message #1682 ] |
Thu, 23 August 2007 12:26 |
bproven
Messages: 12
Registered: August 2007
Location: Fort Collins CO |
Junior Member |
|
|
| chriswest wrote on Thu, 23 August 2007 12:13 |
Well I'm no expert on the optimizer's plans, but I suppose your key is just too long in order to be taken into account for the optimzer. Have you tried to force the use of an index?
left JOIN cases_cstm ON cases.id = cases_cstm.id_c FORCE INDEX (id_c)
|
I did the following (added the FORCE INDEX for the PRIMARY key index in cases.cases_cstm):
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 FORCE INDEX (PRIMARY) 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 | 3087 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | cases_cstm | ALL | NULL | NULL | NULL | NULL | 3139 | |
| 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 | |
+----+-------------+----------------+--------+-------------------------------------------------+---------+---------+-----------------------------------+------+----------------------------------------------+
As you can see no change... 
|
|
|
|
|
|
|
|
|
Members
Search
Help
Register
Login
Home
Re: Left Join Not using index (or how to index this query)?
