How can I speed up this MySQL query

Question

Can someone help me with this query? It's working fine but it take too much time. I determined the problem was too many OR statements in tb_activities. Does anyone have any ideas to make this query faster?

SELECT tb_posts.*
FROM   tb_posts
WHERE  EXISTS (SELECT c_id
               FROM   tb_users
               WHERE  tb_posts.c_uid = tb_users.c_id
                      AND tb_users.c_tokens > 0)
       AND NOT EXISTS (SELECT c_id
                       FROM   tb_activities
                       WHERE  tb_posts.c_url = tb_activities.c_url
                              AND tb_activities.c_category = 'gplus'
                              AND ( tb_activities.c_uid LIKE '%,6x1,%'
                                     OR tb_activities.c_uid LIKE '%,6x1'
                                     OR tb_activities.c_uid LIKE '6x1,%'
                                     OR tb_activities.c_uid = '6x1' ))
       AND NOT EXISTS (SELECT c_id
                       FROM   tb_blacklist
                       WHERE  tb_posts.c_url LIKE Concat('%', tb_blacklist.c_url
                                                  , '%')
                              AND tb_blacklist.c_times > 2
                              AND tb_blacklist.c_category = 'gplus')
       AND tb_posts.c_category = 'classic'
       AND tb_posts.c_status = 'run'
       AND tb_posts.c_nogplus = 0
GROUP  BY tb_posts.c_url
ORDER  BY tb_posts.c_cost DESC,
          tb_posts.c_date DESC
LIMIT  30  

Answer 1

What I did here is rewriting the first WHERE EXISTS .... Why? Here is the answer. Another interesting read is this. So you might consider rewriting the query further. Unfortunately I don't have any more time now. But the main performance boost you will get anyway by adding (compound) indexes. Put indexes on columns on which JOINs are based or columns which are often used in the WHERE clause.

SELECT tb_posts.*
FROM   tb_posts
INNER JOIN tb_users ON tb_posts.c_uid = tb_users.c_id
WHERE  tb_users.c_tokens > 0
       AND NOT EXISTS (SELECT c_id
                       FROM   tb_activities
                       WHERE  tb_posts.c_url = tb_activities.c_url
                              AND tb_activities.c_category = 'gplus'
                              AND ( tb_activities.c_uid LIKE '%,6x1,%'
                                     OR tb_activities.c_uid LIKE '%,6x1'
                                     OR tb_activities.c_uid LIKE '6x1,%'
                                     OR tb_activities.c_uid = '6x1' ))
       AND NOT EXISTS (SELECT c_id
                       FROM   tb_blacklist
                       WHERE  tb_posts.c_url LIKE Concat('%', tb_blacklist.c_url, '%')
                              AND tb_blacklist.c_times > 2
                              AND tb_blacklist.c_category = 'gplus')
       AND tb_posts.c_category = 'classic'
       AND tb_posts.c_status = 'run'
       AND tb_posts.c_nogplus = 0
GROUP  BY tb_posts.c_url
ORDER  BY tb_posts.c_cost DESC,
          tb_posts.c_date DESC
LIMIT  30

Also you might want to read about EXPLAIN, so you know which indexes are used (or not used).

And as a sidenote, Peter Kiss´ tip about changing the order of the WHERE clause is nonsense. The query optimizer handles this anyway.