We help IT Professionals succeed at work.

How to Ensure Index Use in MySQL Join Query

iSac1984
iSac1984 asked
on
2,030 Views
Last Modified: 2013-12-12
Using MySQL 5.0.45

In the MySQL manual page on left join optimization (http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html), it says that in the query:

SELECT *
  FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

MySQL has to perform a full table scan because of the read order forced by left join.

I have a similar query, except that
1) All joins are explicitly left joins
2) All join conditions are given

It looks something like this:

SELECT a.*, b.*, c.*, d.*
FROM a LEFT JOIN b
ON a.primary_key = b.primary_key
LEFT JOIN c
ON a.primary_key = c.primary_key
LEFT JOIN d
ON a.primary_key = d.primary_key
WHERE b.other_key = other_key_value;

The way I think/hope MySQL does this is to join based on what is in table a using the primary key indexes in a, b, c, and d, and then narrow that result set based on b.other_key, with no full table scans.

Am I doing what I want, or am I full table scanning table b?

Thank you for your help!
Comment
Watch Question

Author

Commented:
I should have added:

If I am in fact full table scanning, is there any solution besides putting the table in the where clause (table b) first? It would be possible for me to do this, but I'm guessing it would double to triple the size of b.

Thanks again!
Hi,

Because your WHERE clause references: WHERE b.primary_key = other value, you should reverse your joins as per a snippet from the MySQL page.

I'm not an expert at join optimisation exactly, but that is what I gather from the MySQL page. You could always time it one way, and then time it the other.
The join optimizer calculates the order in which tables should be joined. The table read order forced by LEFT JOIN or STRAIGHT_JOIN helps the join optimizer do its work much more quickly, because there are fewer table permutations to check. Note that this means that if you do a query of the following type, MySQL does a full scan on b because the LEFT JOIN forces it to be read before d: 
SELECT *
  FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;
The fix in this case is reverse the order in which a and b are listed in the FROM clause: 
SELECT *
  FROM b JOIN a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

Open in new window

Author

Commented:
Thanks for the input Nutrient!

I'm thinking/hoping that that particular blurb on the manual page is for optimizing the JOIN CONDITION for joining, as opposed to the WHERE clause for result set narrowing. In other words, the first query on the manual page seems to misguidedly use its WHERE clause as a join condition, instead of for further narrowing after joining, which is what I'm interested in.

Also, my the index on table b used in my WHERE clause is not the primary key.

Does that change anything!

iSac
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I'm close to understanding. Assuming that I keep the query the way it is, MySQL does this:

1) Read A using the primary key, join ALL rows in b, c, and d that match based on said primary key

2) Separately, read B using the location index as specified in the WHERE constraint

3) Find and return the intersection of the two result sets

I'm sorry if I'm being a little dense on this. I know altering table b to go first would solve all problems, but doing so would increase the size of b by a lot, at least twice, if not more.

I guess I ultimately don't mind too much if two reads have to be performed, provided they're performed using the appropriate indexes, and not done with a full table scan.

Author

Commented:
Also, (not sure why I didn't put this before) I had MySQL explain a similar query (see code snippet). And it gave me these results (typing manually because the results come back out of alignment)
 
id   select_type   table   type      possible keys            key        key len   ref                rows   extra
1    simple           t1        all          primary                     null         null         null               4
1    simple           t2        ref         member, location     member  4            t1.memberid  1          Using Where
1    simple           t3        eq_ref   primary                    primary   4           t1.memberid   1

I'm working on deciphering what this means, but I thought I should provide it.

Hope that helps, thanks again.
mysql> explain
    -> select t1.*, t2.*, t3.*
    -> from
    -> a t1 left join b t2
    -> on t1.memberid = t2.memberid
    -> left join c t3
    -> on t1.memberid = t3.memberid
    -> where t2.latitude between 40 and 80
    -> and t2.longitude between -40 and -80;

Open in new window

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.