# Left Join Not using index (or how to index this query)?
**URL:** https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416
**Category:** Other MySQL® Questions
**Created:** [August 18, 2007, 7:14pm UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416 "2007-08-18T19:14:12Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 18, 2007, 7:14pm UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/1 "2007-08-18T19:14:12Z")
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 ( SugarCRM ) 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
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 19, 2007, 11:17am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/2 "2007-08-19T11:17:32Z")
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.
---
### Author:  [@carpii](https://forums.percona.com/u/carpii)
#### Post date: [August 22, 2007, 4:10pm UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/3 "2007-08-22T16:10:55Z")
Go back to your first query and try adding an index on cases(id, case\_number). Maybe that helps?
Make sure any fields which are never null, are marked as NOT NULL.
You could also try adding STRAIGHT\_JOIN to the query to influence the execution planner. Sadly I dont recall the exact theory behind what it does, but I do know Ive had some very good results with it.
Its often trial and error with MySQL
SELECT STRAIGHT\_JOIN field1, field2 FROM etc etc
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 22, 2007, 7:34pm UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/4 "2007-08-22T19:34:53Z")
Thanks for the tips. Its really weird.
Currently the id field has a primary key index on it and the number field is a regular index. I’ll try the multi-column index you suggest and see what happens. The MySQL optimizer is hard to figure out! I’ll see if a straight join helps - in a way I hope it doesn’t because this query is generated by software so its going to be hard to force the STRAIGHT JOIN into the query (have to dig through PHP code ( ) I was hoping a few well placed indexes would get this sucker to speed up without rewriting the query.
BTW SHOW INDEX gives me the below (cases table):
±------±-----------±--------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |±------±-----------±--------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+| cases | 0 | PRIMARY | 1 | id | A | 3061 | NULL | NULL | | BTREE | NULL || cases | 1 | case\_number | 1 | case\_number | A | NULL | NULL | NULL | | BTREE | NULL || cases | 1 | idx\_case\_name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | NULL |±------±-----------±--------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 22, 2007, 7:48pm UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/5 "2007-08-22T19:48:55Z")
| [B]carpii wrote on Wed, 22 August 2007 17:40[/B] |
| Go back to your first query and try adding an index on cases(id, case\_number). Maybe that helps?
Make sure any fields which are never null, are marked as NOT NULL.
You could also try adding STRAIGHT\_JOIN to the query to influence the execution planner. Sadly I dont recall the exact theory behind what it does, but I do know Ive had some very good results with it.
Its often trial and error with MySQL
SELECT STRAIGHT\_JOIN field1, field2 FROM etc etc
|
No luck (
I tried adding the cases(id, case\_number) index and using the STRAIGHT\_JOIN syntax - same results. Slow, slow, slow.
---
### Author:  [@carpii](https://forums.percona.com/u/carpii)
#### Post date: [August 23, 2007, 1:39am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/6 "2007-08-23T01:39:03Z")
what about an index just on cases.case\_number ?
Does the explain change at all?
Whats the primary key on cases. Is it really 108 bytes?
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 23, 2007, 8:32am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/7 "2007-08-23T08:32:31Z")
According to the SHOW INDEX above, I already have an index on cases.case\_number. So it doesn’t appear to do anything. How do I find the index length? SHOW INDEX doesn’t tell me key length, I guess from the EXPLAIN in a previous post it states that the “key\_len” is 108. IS there another command I can run to display this?
---
### Author:  [@chriswest](https://forums.percona.com/u/chriswest)
#### Post date: [August 23, 2007, 10:01am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/8 "2007-08-23T10:01:17Z")
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.
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 23, 2007, 10:29am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/9 "2007-08-23T10:29:08Z")
| [B]chriswest wrote on Thu, 23 August 2007 11:31[/B] |
| Are you sure there is a primary key / index on [B]cases\_cstm.id\_c[/B]?
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)
---
### Author:  [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [August 23, 2007, 10:37am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/10 "2007-08-23T10:37:40Z")
When you are using a LEFT JOIN you are forcing the DBMS to perform the join in order left to right.
When you are changing to use an INNER JOIN the optimizer can choose the join order freely and that is why your query is fast with an INNER JOIN since it chooses the right to left order instead since you have a condition on the right table.
The STRAIGHT JOIN syntax is just an INNER JOIN where you are forcing the join order to left to right.
But my question is if you have an index on cases\_cstm.id\_c?
Since the join order is cases-\>cases\_cstm that is the index that you need.
---
### Author:  [@chriswest](https://forums.percona.com/u/chriswest)
#### Post date: [August 23, 2007, 10:43am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/11 "2007-08-23T10:43:46Z")
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)
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 23, 2007, 10:43am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/12 "2007-08-23T10:43:52Z")
| [B]sterin wrote on Thu, 23 August 2007 12:07[/B] |
| When you are using a LEFT JOIN you are forcing the DBMS to perform the join in order left to right.
When you are changing to use an INNER JOIN the optimizer can choose the join order freely and that is why your query is fast with an INNER JOIN since it chooses the right to left order instead since you have a condition on the right table.
The STRAIGHT JOIN syntax is just an INNER JOIN where you are forcing the join order to left to right.
But my question is if you have an index on cases\_cstm.id\_c?
Since the join order is cases-\>cases\_cstm that is the index that you need.
|
OK - thanks for the great explanation. That makes sense. To answer your question, there is a primary key index on cases\_cstm.id\_c as shown in the post above yours. I might have posted it at the same time you posted your response…
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 23, 2007, 10:56am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/13 "2007-08-23T10:56:14Z")
| [B]chriswest wrote on Thu, 23 August 2007 12:13[/B] |
| 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… (
---
### Author:  [@chriswest](https://forums.percona.com/u/chriswest)
#### Post date: [August 23, 2007, 11:03am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/14 "2007-08-23T11:03:12Z")
I just noticed something:
CREATE TABLE `cases` ( …) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
and
CREATE TABLE `cases_cstm` ( … ) ENGINE=MyISAM DEFAULT CHARSET=latin1
you have different charsets for both tables - and you are joining on char columns: make those two charsets identical )
both utf-8 or both latin1
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 23, 2007, 11:21am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/15 "2007-08-23T11:21:01Z")
| [B]chriswest wrote on Thu, 23 August 2007 12:33[/B] |
| I just noticed something:
CREATE TABLE `cases` ( …) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
and
CREATE TABLE `cases_cstm` ( … ) ENGINE=MyISAM DEFAULT CHARSET=latin1
you have different charsets for both tables - and you are joining on char columns: make those two charsets identical )
both utf-8 or both latin1
|
LOL - I think that may be the solution! Didnt notice that at all!
Is it OK to just change the charset/collation on that column only? Just to be safe since for the rest of the table char columns. I was thinking of issuing this:
ALTER TABLE `cases_cstm` MODIFY COLUMN `id_c` CHAR(36) COLLATE utf8\_general\_ci NOT NULL
---
### Author:  [@chriswest](https://forums.percona.com/u/chriswest)
#### Post date: [August 23, 2007, 11:28am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/16 "2007-08-23T11:28:07Z")
no that won’t do it:
- collations tell MySQl how to order / sort strings in a specific charset
- the charset specifies which character set is used to encode / decode a stored string properly
so you need to change the charset of the whole table - I do not know if you can change the charset for a specific column )
---
### Author:  [@bproven](https://forums.percona.com/u/bproven)
#### Post date: [August 23, 2007, 11:40am UTC](https://forums.percona.com/t/left-join-not-using-index-or-how-to-index-this-query/416/17 "2007-08-23T11:40:36Z")
| [B]chriswest wrote on Thu, 23 August 2007 12:58[/B] |
| no that won't do it:
- collations tell MySQl how to order / sort strings in a specific charset
- the charset specifies which character set is used to encode / decode a stored string properly
so you need to change the charset of the whole table - I do not know if you can change the charset for a specific column )
|
Was looking here:
[http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.ht](http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.ht) ml
Looks like its possible. I guess I can do the following to change the charset on the column and the collation as well. I guess I should change the collation while I change charset(?) It looks like “cases.id” is set to charset ‘utf8’ and collation of ‘utf\_general\_ci’.
ALTER TABLE `cases_cstm` MODIFY `id_c` CHAR(36) CHARACTER SET utf8;ALTER TABLE `cases_cstm` MODIFY COLUMN `id_c` CHAR(36) COLLATE utf8\_general\_ci NOT NULL
Don’t want to hose anything up changing charsets for the other char columns. Not sure if this is a unnecessary fear of mine… I’ll try this in dev when I get a chance and see what comes of it.
BTW thanks a MILLION for spotting this! 😃