Re: Slow query_time with heavy locking

[Rick James]

> AND article.ostart+article.otime>=1360061149
This will not optimize well. That is, the INDEXes are useless.

> (article.contenttype=75 OR article.articletype=0)
-->
article.contenttype IN (75,0)

> WHERE (candidateinstmainpage=1 AND article.contenttype IN (75,0)) AND article.psmandantor=18
Begs for a 'compound' index:
INDEX(candidateinstmainpage, psmandantor, contenttype)

MyISAM? Seriously consider switching to InnoDB; it suffers from less lock contention.

> Query_time: 252.156515 Lock_time: 251.136899
The query was not slow (only about 1 second); something else was the villain.
If you see a slowdown, do SHOW PROCESSLIST and find the (one) query that is not "Locked" or "Sleep".

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?