CODEWITHSUNDEEP
mysqlsqloptimization
Urmelinho
Urmelinho

Reputation: 1997

MySQL is not using prmary index

I have this query:

SELECT SQL_NO_CACHE
    COUNT(*) AS `numrows`
FROM
    (`citations`)
        LEFT JOIN
    `projects` ON `projects`.`project_id` = `citations`.`project_id`
        LEFT JOIN
    `users` ON `users`.`user_id` = `projects`.`user_id`
WHERE
    `users`.`role` = '0'
        AND `citations`.`created` BETWEEN 1360213200 AND 1360299599
        AND `citations`.`in_card` = '0'
        AND `citations`.`citation_id` NOT IN (SELECT 
            user_stats_citations.citation_id
        FROM
            user_stats_citations,
            user_stats FORCE INDEX (user_stats_type_index)
        WHERE
            user_stats_citations.user_stat_id = user_stats.id
                AND user_stats.type IN (69 , 70, 71, 75, 76));

I have those indexes on user table:

users            0  PRIMARY                             1  user_id      A                42836    (NULL)  (NULL)          BTREE                               
users            1  users_industry_id_index             1  industry_id  A                  118    (NULL)  (NULL)  YES     BTREE                               
users            1  users_sponsor_index                 1  sponsor      A                   12    (NULL)  (NULL)  YES     BTREE

This is the output of EXPLAIN EXTENDED

  id    select_type table   type    possible_keys   key key_len ref rows    filtered    Extra

  1 PRIMARY users   ALL PRIMARY \N  \N  \N  42836   100.00  Using where
  1 PRIMARY projects    ref PRIMARY\,projects_user_id_index projects_user_id_index  4   citelighter.users.user_id   1   100.00  Using where; Using index
  1 PRIMARY citations   ref citations_project_id_index  citations_project_id_index  4   citelighter.projects.project_id 4   100.00  Using index condition; Using where
  2 SUBQUERY    user_stats  range   user_stats_type_index   user_stats_type_index   2   \N  410768  100.00  Using where; Using index
  2 SUBQUERY    user_stats_citations    ref user_stats_citations_index_user_stat_id\,user_stats_citations_index_citation_id user_stats_citations_index_user_stat_id 8   citelighter.user_stats.id   1   100.00  \N

I tried to add FORCE INDEX on users LEFT JOIN but the index is not used. Can you help me to solve this, because this query is taking like 10 seconds on my local and 1 second on production environment.

Upvotes: 0

Views: 63

Answers (2)

GarethD
GarethD

Reputation: 69769

The first thing I notice is that this predicate in the where clause: WHERE users.role = '0' turns your LEFT JOINs to INNER JOINs, so you may as well just make them inner joins.

Secondly, MySQL has problems optimising correlated subqueries, and also can perform poorly with derived tables. e.g. In this simple query:

SELECT *
FROM (SELECT * FROM T) T
JOIN (SELECT * FROM T) T2 ON T.ID = T2.ID;

Even though ID is the primary key on T, the primary key is not used for the join as it can't be cascaded out of the derived table. Similarly sometimes when you write:

SELECT *
FROM T
WHERE Afield NOT IN (SELECT Afield FROM T WHERE AnotherField = 1);

MySQL does not necessarily materialise the subquery and use this, it will often rewrite the query as:

SELECT *
FROM T
WHERE NOT EXISTS (SELECT 1 
                    FROM T T2 
                    WHERE T.Afield = T2.Afield  
                    AND T2.AnotherField = 1);

And the subquery is executed for each row in the outer query, so if you have a large number of rows in the outer query executing the subquery for every row becomes very costly. The solution is to avoid subqueries as far as possible. In your case you can rewrite your query as:

SELECT  SQL_NO_CACHE
        COUNT(*) AS `numrows`
FROM    `citations`
        INNER JOIN `projects` 
            ON `projects`.`project_id` = `citations`.`project_id`
        INNER JOIN `users` 
            ON `users`.`user_id` = `projects`.`user_id`
        LEFT JOIN (user_stats_citations
            INNER JOIN user_stats
                ON user_stats_citations.user_stat_id = user_stats.id
                AND user_stats.type IN (69 , 70, 71, 75, 76))
            ON user_stats_citations.citation_id = `citations`.`citation_id`
WHERE   `users`.`role` = '0'
AND     `citations`.`created` BETWEEN 1360213200 AND 1360299599
AND     `citations`.`in_card` = '0'
AND      user_stats_citations.citation_id IS NULL;

With no subqueries there is no derived tables, or row by row execution of subqueries. This should improve execution time.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

What does this give you?

SELECT COUNT(*) numrows
  FROM citations c
  JOIN projects p
    ON p.project_id = c.project_id
  JOIN users u
    ON u.user_id = p.user_id

  LEFT
  JOIN 
     ( SELECT uc.citation_id
         FROM user_stats_citations uc
         JOIN user_stats us
           ON uc.user_stat_id = us.id
          AND us.type IN (69,70,71,75,76)
     ) x
    ON x.citation_id = c.citation_id
 WHERE u.role = 0
   AND c.created BETWEEN 1360213200 AND 1360299599
   AND c.in_card = 0
   AND x.citation_id IS NULL

Upvotes: 0

Related Questions