CODEWITHSUNDEEP
mysqlsqlquery-optimization
user1016265
user1016265

Reputation: 2387

Why is my MySQL query is so slow?

I'm trying to figure out why that query so slow (take about 6 second to get result)

SELECT DISTINCT
    c.id
FROM
    z1
        INNER JOIN
    c ON (z1.id = c.id)
        INNER JOIN
    i ON (c.member_id = i.member_id)
WHERE
    c.id NOT IN (... big list of ids which should be excluded)

This is execution plan

+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+--------------------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref                | rows   | filtered | Extra                    |
+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | z1    | index  | PRIMARY           | PRIMARY | 4       | NULL               | 318563 |    99.85 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,member_id | PRIMARY | 4       | z1.id              |      1 |   100.00 |                          |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY           | PRIMARY | 4       | c.member_id        |      1 |   100.00 | Using index              |
+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+--------------------------+

is it because mysql has to take out almost whole 1st table ? Can it be adjusted ?

Upvotes: 0

Views: 104

Answers (3)

symcbean
symcbean

Reputation: 48357

It is imposible to say from the information you've provided whether there is a faster solution to obtaining the same data (we would need to know abou data distributions and what foreign keys are obligatory). However assuming that this is a hierarchical data set, then the plan is probably not optimal: the only predicate to reduce the number of rows is c.id NOT IN.....

The first question to ask yourself when optimizing any query is Do I need all the rows? How many rows is this returning?

I'm struggling to see any utlity in a query which returns a list of 'id' values (implying a set of autoincrement integers).

You can't use an index for a NOT IN (or <>) hence the most eficient solution is probably to start with a full table scan on 'c' - which should be the outcome of StanislavL's query.

Since you don't use the values from i and z, the joins could be replaced with 'exists' which may help performance.

Upvotes: 1

Karolis
Karolis

Reputation: 9562

I would consider creating a compound index for c(id, member_id). This way the query should work at index level only without scanning any rows in tables.

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

You can try to replace c with a subquery.

SELECT DISTINCT
    c.id
FROM
    z1
        INNER JOIN
    (select c.id 
     from c 
     WHERE
       c.id NOT IN (... big list of ids which should be excluded)) c ON (z1.id = c.id)
        INNER JOIN
    i ON (c.member_id = i.member_id)

to leave only necessary id's

Upvotes: 1

Related Questions