Re: Problem with multiple queries using a composite index
Posted by:
Rick James
Date: April 03, 2011 07:11AM
Do your test again, but this time run this one first:
SELECT * FROM MyTable WHERE (Field1, Field2, Field3, Field4, Field5) = (1, 1, 2, 3, 5);
Then run this one:
SELECT * FROM MyTable WHERE Field1=1 AND Field2=1 AND Field3=2 AND Field4=3 AND Field5=5;
Why? There are multiple caches involved in queries. If this experiment give you the opposite conclusion, then the faster times are artifacts of some cache.
Also please run EXPLAIN SELECT ... using each of them.
My understanding is that WHERE (a,b)=(1,2) is poorly optimized, and should be avoided. You seem to have contradictory information; I would like to get to the bottom of it.
> So, it seems that composite keys cannot be used as such (the second kind of query) when concurrent queries are issued.
This seems unlikely. But, again, it is possibly something I am overlooking.
> InnoDB one, but that grew over 20GiB
Yes, InnoDB's disk footprint is 2x-3x that of MyISAM. You probably now have a big ibdata1 file that you can't get rid of.
SHOW VARIABLES LIKE 'query%';
SHOW VARIABLES LIKE '%buffer%';
How much RAM do you have?