Mysql performance: A big query that always stuck/slow

Question

I have the following query, which always makes the whole website down becuase it's "waiting for table level lock":

/*IPS\Patterns\_ActiveRecordIterator::count:246*/ SELECT SQL_CALC_FOUND_ROWS forums_posts . * , forums_topics . * 
FROM  `forums_posts` 
LEFT JOIN  `forums_topics` ON forums_posts.topic_id = forums_topics.tid
LEFT JOIN  `core_permission_index` ON core_permission_index.app =  'forums'
AND core_permission_index.perm_type =  'forum'
AND core_permission_index.perm_type_id = forums_topics.forum_id
LEFT JOIN  `forums_forums` ON forums_topics.forum_id = forums_forums.id
WHERE (
NULLIF( forums_topics.moved_to,  '' ) IS NULL
)
AND (
forums_forums.password IS NULL 
OR (
(
FIND_IN_SET( 2, forums_forums.password_override )
)
)
)
AND (
forums_forums.can_view_others =1
OR forums_topics.starter_id IS NULL
)
AND forums_forums.min_posts_view <=0
AND queued =0
AND forums_topics.approved =1
AND (
(
(
FIND_IN_SET( 2, perm_2 )
)
)
OR perm_2 =  '*'
)
ORDER BY post_date DESC 
LIMIT 185725 , 25

Is there anything I can do to improve the performance of this query instead of killing the proccess/ wait 10 minutes untils it's finished?

Thats because of [SQL_CALC_FOUND_ROWS](http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count) — Fal, Feb 20 '17 at 15:06

score 0 · Accepted Answer · answered Feb 19 '17 at 23:11

If this is a "crawler" that is hosing your web site, the you really must get rid of that use of OFFSET. Or get some filters to prevent what is effectively a DOS (Denial Of Service) Attack. At least tell the search bots to avoid your site. (That may be the simple answer.)

Let's get real. LIMIT 185725 , 25 -- That's page number 7429. Did you really step through that many pages?

What do you have that has so many rows, yet has no convenient way to "search" or "consolidate" the data??

Other issues:

OR is costly -- it prevents index usage

LEFT JOIN often requires looking into the 'right' table, but being happy when nothing is there. This can have worse performance than plain JOIN. (However, perhaps the business logic requires it.)

Because of the complexity of the query, SQL_CALC_FOUND_ROWS is likely to take almost as long as fetching all the rows without the LIMIT. Notice how the search engines used to say "About 180,000" results found, then even dropped that. Perhaps your app should do likewise.

Surely there is a simpler way to do NULLIF( forums_topics.moved_to, '' ) IS NULL.

What Engine are you using for the tables? If using MyISAM, switch to InnoDB; that will probably get rid of the "table level lock".

score 0 · Answer 2 · answered Feb 20 '17 at 14:48

Please check that the table type is InnoDB. If it's MyISAM, convert it to InnoDB. MyISAM is not able to read the data in a table while some other thread/query is updating any rows in that table. It uses table-level locks.

InnoDB uses MVCC to enable SELECTs while the table is being updated by UPDATEs/INSERTs/DELETEs.

Mysql performance: A big query that always stuck/slow

2 Answers2