Re: Problem with multiple queries using a composite index
Posted by:
Rick James
Date: April 04, 2011 11:30PM
This is really bad:
query_cache_size = 2G
50M is about the max to use before it starts causing trouble.
After shrinking the QC, raise the buffer_pool. It should be more like 13G.
SELECT will consider using the QC; UPDATE will flush it.
MyISAM and InnoDB have radically different internal designs.
As for InnoDB and the PRIMARY KEY, etc -- please provide SHOW CREATE TABLE and the actual queries. (Partially because I am lost as to which query is the 'first'.)
WHERE (Field1, Field2, Field3, Field4, Field5) = (1, 1, 2, 3, 5)
is not as well optimized as
WHERE Field1=1 AND Field2=1 AND Field3=2 AND Field4=3 AND Field5=5
If you have
INDEX(f1, f2, f3, f4, f5),
INDEX(f6)
note that it will use one or the other of the indexes, not both.
Yes, the primary key is included in every secondary key for InnoDB, but your description was too vague to go into detail -- What field(s) were fetched? Were you doing a subquery? Were you fetching the PK in one statement, then using the PK to find the row? Or is it multiple rows? How big are the rows? (Big blobs have a different dynamic.)
> So in order to reduce the needed space, I inserted that primary key.
Inserted it where? Changing INDEX(f6) to INDEX(f6, PK) won't change the size of that secondary key.