Re: Why are no indexes used in this case?
Posted by:
Rick James
Date: February 04, 2013 09:11PM
> This particular query used to take 15s in my version for 3 returned records, while your version takes 0.0017s :))
That's my specialty. ;)
> Maybe some guy from mysql can pick it up and see if it can be optimised.
IN ( SELECT ... ) is being better optimized in 5.6.9, and perhaps MariaDB 5.5. Yes, that is long overdue.
Subqueries, in general, should be avoided until 5.6 or MariaDB. Oh, you noticed I used a subquery? Well... My subquery uses only indexes, and digests stuff down (HAVING). Then it easily JOINs to the outer table. The temp table generated by the subquery must be fully scanned, but that is ok -- in a JOIN, one side of the JOIN always (almost always) is fully scanned, then an efficient "Nested Loop Join" is done to the other table. Again, I made sure the necessary index was present.