CODEWITHSUNDEEP
mysqlindexinginnodb
Colleen
Colleen

Reputation: 25489

low cardinality index still slows query down

using MySQL 5.5 with InnoDB. have a query like

    SELECT
        count(distinct a.thing_id) as new_thing_count,
        sum(b.price) as new_thing_spend
    FROM thing ii
    LEFT OUTER JOIN thing a
        ON a.customer_id = ii.customer_id
        AND a.created_at >= '2013-01-01'
        AND a.created_at <= '2013-03-31'
    JOIN whatsit b
        ON b.whatsit_id = a.original_whatsit_id
    WHERE ii.customer_id = 3

where

Now, when I run this query with an index on customer_id, it takes about 10 seconds. When I drop the index, it takes .03 seconds.

I can't figure out why this might be. Here's the result of explain without the index:

1   SIMPLE  ii  ALL                 24937   Using where
1   SIMPLE  a   ALL                 24937   Using where; Using join buffer
1   SIMPLE  b   eq_ref  PRIMARY PRIMARY 4   db.a.original_whatsit_id    1

here it is with the index (thing_customer)

1   SIMPLE  ii  ref thing_customer  thing_customer  4   const   3409    Using index
1   SIMPLE  a   ref thing_customer  thing_customer  4   const   3409    Using where
1   SIMPLE  b   eq_ref  PRIMARY PRIMARY 4   db.a.original_whatsit_id    1

Can someone help me interpret why this index is slowing things down SO much when logically it doesn't seem like it should?

Upvotes: 1

Views: 138

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78473

When your DB engine decides to read the index, it reads rows one by one in order. This can lead it to read a row in disk page 2, another in page 4, another in page 1, the next in page 2, etc.

Sometimes, the going back and forth is such that the index is not going help -- on the contrary.

If the DB engine does a poor job at collecting and analyzing statistics on the table while generating the query plan, it might not identify that the index yields completely fragmented disk reads. This is likely what you're experiencing.

Try analyzing the tables to collect new stats:

http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html

And then retry with and without the index.

Upvotes: 2

Related Questions