Re: Problem with multiple queries using a composite index
Posted by:
Rick James
Date: April 05, 2011 09:48PM
> It changes 1 row of the table and needs for that purpose about 1 minute 12sec.
I really suspect it is the search through the 2GB query_cache for all entries for that table. They have to be 'deleted' from the cache. Change it to 50M.
> It takes exactly 3 minutes for the first time, but less than one second for the second, identical execution.
Again -- it could be that the cache was 'full' the first time, so there was lots to 'delete'. The second time, it was effectively empty.
I don't know the details of the QC implementation, but I could imagine scenarios where your timings make sense.
> WHERE clause is of the form (,,,,) = (,,,,)
That could be coincidence, or it could be the poor optimization of that syntax.
> SELECT case (all significantly below one second)
SELECT does not have to purge the QC. Also, if the identical query is run twice (without a purge between), it will get the resultset from the QC -- extremely rapidly.
> rows are not necessarily accessed row by row since it's a parallel application...
The UPDATE (with a 'perfect' index) would, at worst, have to hit the disk only a few times. So the UPDATE, without other things going on, and without having to purge the QC, will 'always' run in less than a second.
Bottom line:
Much smaller QC; larger buffer_pool.