Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table. I'm not sure if the index is in fact being used and it simply can't be seen in the EXPLAIN output, or if in fact for some reason the index isn't useful (either in this particular case or in general).

For example:

Table 1:
ex_cat, ex_name
(Primary key on ex_cat.)

Table 2: 
cat, ex_cat, data
(Composite primary key on cat, ex_cat; separate
 non-unique index on ex_cat?)

I am running the following query:

SELECT * 
FROM table1 INNER JOIN table2 
     ON table1.ex_cat = table2.ex_cat 
WHERE table2.cat = 'value' 
ORDER BY table2.data;

It gives me the following EXPLAIN output, regardless of whether I have an index on the ex_cat column in table 2 or not:

id  select_type  table   type    possible_keys  key      key_len  ref             rows  Extra
1   SIMPLE       table2  ref     PRIMARY        PRIMARY  22       const           1     Using where; Using filesort
1   SIMPLE       table1  eq_ref  PRIMARY        PRIMARY  32       table2.ext_cat  1     Using where

Which seems right; it's narrowing using the WHERE clause on the parent table primary key, then it's joining the result using the ext_cat column in the parent table and the primary key in the child table. However, since it's the same either way, there's nothing to explicitly show whether there is value in having an index on table2.ext_cat. The tables are so small the queries complete near-instantly either way, so this is more out of academic interest at the moment.

I also tried switching to InnoDB to see if there was any difference in behaviour (not officially using foreign keys, since those would create indexes automatically; just the same test as above) but the explain output was identical.

So, is it indeed improving query performance to have an index on the foreign key? If so, is there some way to 'see' the effect with an EXPLAIN (or otherwise)? And if not, can you explain what about this situation causes it to be unnecessary?

Edit: OK, I expect what's going on is because MySQL is choosing to first narrow using the WHERE clause, then join the result to the child table, any indexes on the parent table are irrelevant at that point. In cases where it is more efficient to join first, then apply the clause, it would use the foreign key index in the join. Is that correct, and do both behaviors indeed occur depending on the table contents and the query? If not, when are indexes on foreign keys useful?

Edit 2: According to this it would appear the answer is never, if the parent table appears first in the EXPLAIN: mysql-indexes. An index is only used on one half of a join, since it essentially goes one table at a time, joining the result to the next table in the cascade.

share|improve this question

migrated from stackoverflow.com May 30 '13 at 13:36

This question came from our site for professional and enthusiast programmers.

    
Have you tried the query without the WHERE part? Does the index appear in the explain part then? –  ypercube May 30 '13 at 17:51
    
I have, and no, it doesn't. The first line of the explain still references the second table (with an 'ALL'), and the second line is the same except for a higher rows number. My understanding from the link in the second edit is that as long as table 2 appears first in the explain, the primary key on table 1 (only) is what will be used for the join. An index on table 2's foreign key would be used if MySQL decided to operate on table 1 first, but I'm not sure in what situation (if any) that would happen. –  Nathan Stretch May 30 '13 at 19:57
    
I'm guessing either it always operates on the parent table first (the child being the one that's joined on its primary key), and/or that the optimizer is deciding this order is more efficient due to the sizes or contents of the tables, even without a WHERE clause. When I get a chance I'll play with some different data to test that, unless someone can confirm the behavior. –  Nathan Stretch May 30 '13 at 19:59
    
There is no "always" but yes it decides which order is more efficient (less "costly")... but there are a couple of aspects in your EXPLAIN that I can't duplicate, either with MyISAM, InnoDB, or a mix. I get ref = const and type = const and no using where -- which is what I would expect. Using where does not exactly mean what you think it means (and the docs are ambiguous), and the filesort makes no sense either, since this query could only ever return 1 row and the optimizer should realize that. Please post your actual table definitions and MySQL version. –  Michael - sqlbot May 31 '13 at 3:41
    
You're right Michael; that was my mistake in trying to generalize the data to post here. Can't post the source tables unfortunately. The issue was that in reality table 2 has a composite primary on cat and ex_cat, not just a primary on cat (I've updated the question), so the WHERE clause on cat doesn't give a const type. When I stripped out duplicate cat values and made cat the primary index alone, it removed the USING WHERE and FILESORT and switched both types to const, as you were getting. I'm curious what you mean by USING WHERE not meaning what I think though; can you elaborate? –  Nathan Stretch May 31 '13 at 9:09
add comment

1 Answer 1

Very quick answer: MyISAM does not support FOREIGN KEYs at all... Any FOREIGN KEY you define on a MYISAM table is silenty thrown away.

When you later switch to InnoDB (i.e. ALTER TABLE my_table ENGINE=InnoDB) the FOREIGN KEY is not there either; it was thrown away when the table used to be MyISAM.

share|improve this answer
    
Yes, I'm using 'foreign key' here to mean 'the field that is being joined to the primary key of the other table'. I'm aware that there are no official foreign keys on MyISAM tables, and wrote this in the question: "I also tried switching to InnoDB to see if there was any difference in behaviour (not officially using foreign keys, since those would create indexes automatically; just the same test as above) but the explain output was identical." (So I manually created the index in both cases.) –  Nathan Stretch May 31 '13 at 21:29
    
I understand now that only one index is ever used in a join; it just depends which table the optimizer decides to operate on first (which effectively answers the original question). For completeness now though, I'm curious in what sort of case it would choose to operate on the child table first, in which case the index on the 'foreign key' would be used for the join. –  Nathan Stretch May 31 '13 at 21:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.