[SOLVED] SQL Index Problem

[danesc]

Greetings,

 

I have the following table:

 

mysql> describe user_history;

+-----------------------+-------------+------+-----+---------------------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------------------+-------------+------+-----+---------------------+----------------+

| user_history_id | int(11) | NO | PRI | NULL | auto_increment |

| user_id | int(11) | NO | MUL | NULL | |

| user_history_event | smallint(6) | NO | MUL | 0 | |

| user_history_detail | int(11) | NO | | NULL | |

| user_history_datetime | datetime | NO | MUL | 0000-00-00 00:00:00 | |

| user_history_ip | varchar(15) | NO | | | |

| media_owner_user_id | int(11) | YES | | 0 | |

| playlist_id | int(11) | YES | | 0 | |

+-----------------------+-------------+------+-----+---------------------+----------------+

8 rows in set (0.14 sec)

 

 

With the following Index:

 

ysql> show index from user_history;

+--------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+--------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

| user_history | 0 | PRIMARY | 1 | user_history_id | A | 5771011 | NULL | NULL | | BTREE | |

| user_history | 1 | user_history_event | 1 | user_history_event | A | 53 | NULL | NULL | | BTREE | |

| user_history | 1 | user_id | 1 | user_id | A | 22720 | NULL | NULL | | BTREE | |

| user_history | 1 | user_history_datetime | 1 | user_history_datetime | A | 2885505 | NULL | NULL | | BTREE | |

+--------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

4 rows in set (0.00 sec)

 

 

I'm trying to execute a simple SQL statement that grabs the rows based on a user_id and sorts the results by user_history_datetime in DESC order.

 

mysql> EXPLAIN SELECT * FROM user_history WHERE user_id=72 ORDER BY user_history_datetime LIMIT 0,25;

+----+-------------+--------------+------+---------------+---------+---------+-------+-------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+------+---------------+---------+---------+-------+-------+-----------------------------+

| 1 | SIMPLE | user_history | ref | user_id | user_id | 4 | const | 34103 | Using where; Using filesort |

+----+-------------+--------------+------+---------------+---------+---------+-------+-------+-----------------------------+

1 row in set (0.00 sec)

 

 

Now, EVEN IF I add under the WHERE statement a range on the user_history_datetime to match what I'm sorting by I still get the same issue of "Using WHere; Using filesort"

 

mysql> EXPLAIN SELECT * FROM user_history WHERE user_id=72 AND user_history_datetime > date('2009-03-20') ORDER BY user_history_datetime LIMIT 0,25;

+----+-------------+--------------+------+-------------------------------+---------+---------+-------+-------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+------+-------------------------------+---------+---------+-------+-------+-----------------------------+

| 1 | SIMPLE | user_history | ref | user_id,user_history_datetime | user_id | 4 | const | 34070 | Using where; Using filesort |

+----+-------------+--------------+------+-------------------------------+---------+---------+-------+-------+-----------------------------+

1 row in set (0.00 sec)

 

 

Any help on this I would greatly appreciate it. Bottom line, I NEED to get rid of the "Using where; Using filesort"

 

Thank you in advance....

[fenway]

You don't want to get rid of "using where" -- otherwise you'll have a full table scan.

 

As for the filesort, it's not using the index on user_history_datetime because it has "decided" that it's faster to narrrow down the table using the user_id instead.  How big is your table? It seems to find ~30K matching user_id rows.  Have you run OPTIMIZE lately?  What table type?

[danesc]

Fenway, thank you for your response.

 

You are correct, I DO NOT want to get rid of the "using where", I was referring to the "filesort".

 

The table is MyISAM, I just ran OPTIMIZE and it did not make any difference ;-( ... the table has 5,815,993  rows... I want to make sure I can sort in DESC order by user_history_datetime and only using INDEX so that the "EXTRA" is only "Using Where"

 

Thank you so much!

[fenway]

Well, you can "force" the other index to be used, but I'm not sure it would be faster... try it and see (make sure the cache is off).

 

EXPLAIN SELECT * FROM user_history FORCE INDEX( user_history_datetime ) WHERE user_id=72 ORDER BY user_history_datetime LIMIT 0,25

[danesc]

Fenway, I tried forcing the index but that is actually slower; Specially if we don't have a record for a particular user_id. A way to think about this table is similar to twitter feeds or facebook feeds where it quickly shows you what others have done in the network. This is to show the same thing, therefore it is crucial we search based on the user_id in decending order of activity.... but for some strange reason I can't get that to work... any other suggestions?

 

Thank you in advance.

[fenway]

Well, of course it's slower... that's why mysql made the "right" choice.  Much better to find 30K (0.5%) records and sort them, then to sort all 5M!!!

 

I'd say that you could make a composite index on ( user_id, user_history_datetime ), but I'm not sure that it would work since you want DESC for the 2nd half of the key.

 

In fact, try it 'backwards' -- composite index on ( user_history_datetime, user_id ).

[danesc]

Fenway, how would I do that? What's a sample query?

 

[fenway]

ALTER yourTable ADD INDEX user_history_datetime_user_id ( user_history_datetime, user_id );

 

Make sure this index is actually being used in the EXPLAIN -- otherwise, you'll have to force it.

[danesc]

I added the new index

 

mysql> show index from user_history;

+--------------+------------+-------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

| Table        | Non_unique | Key_name                      | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+--------------+------------+-------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

| user_history |          0 | PRIMARY                      |            1 | user_history_id      | A        |    5821103 |    NULL | NULL  |      | BTREE      |        |

| user_history |          1 | user_history_event            |            1 | user_history_event    | A        |          53 |    NULL | NULL  |      | BTREE      |        |

| user_history |          1 | user_id                      |            1 | user_id              | A        |      22827 |    NULL | NULL  |      | BTREE      |        |

| user_history |          1 | user_history_datetime        |            1 | user_history_datetime | A        |    2910551 |    NULL | NULL  |      | BTREE      |        |

| user_history |          1 | playlist_id                  |            1 | playlist_id          | A        |        1877 |    NULL | NULL  | YES  | BTREE      |        |

| user_history |          1 | user_history_datetime_user_id |            1 | user_history_datetime | A        |    2910551 |    NULL | NULL  |      | BTREE      |        |

| user_history |          1 | user_history_datetime_user_id |            2 | user_id              | A        |    2910551 |    NULL | NULL  |      | BTREE      |        |

+--------------+------------+-------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

7 rows in set (0.00 sec)

 

 

Here is what I get with the following queries:

 

mysql> EXPLAIN SELECT * FROM user_history WHERE user_id=73 ORDER BY user_history_datetime DESC LIMIT 0,25;

+----+-------------+--------------+------+---------------+---------+---------+-------+--------+-----------------------------+

| id | select_type | table        | type | possible_keys | key    | key_len | ref  | rows  | Extra                      |

+----+-------------+--------------+------+---------------+---------+---------+-------+--------+-----------------------------+

|  1 | SIMPLE      | user_history | ref  | user_id      | user_id | 4      | const | 302877 | Using where; Using filesort |

+----+-------------+--------------+------+---------------+---------+---------+-------+--------+-----------------------------+

1 row in set (0.00 sec)

 

 

 

OR

 

mysql> EXPLAIN SELECT * FROM user_history WHERE user_id=73 AND user_history_datetime < date('2009-04-02') ORDER BY user_history_datetime DESC LIMIT 0,25;

+----+-------------+--------------+------+-------------------------------------------------------------+---------+---------+-------+--------+-----------------------------+

| id | select_type | table        | type | possible_keys                                              | key    | key_len | ref  | rows  | Extra                      |

+----+-------------+--------------+------+-------------------------------------------------------------+---------+---------+-------+--------+-----------------------------+

|  1 | SIMPLE      | user_history | ref  | user_id,user_history_datetime,user_history_datetime_user_id | user_id | 4      | const | 302878 | Using where; Using filesort |

+----+-------------+--------------+------+-------------------------------------------------------------+---------+---------+-------+--------+-----------------------------+

1 row in set (0.00 sec)

 

 

Thank you so much for your continued support.... I hope we can resolve this ;-)

 

[danesc]

Here is forcing the new index

 

mysql> EXPLAIN SELECT * FROM user_history FORCE INDEX( user_history_datetime_user_id ) WHERE user_id=73 ORDER BY user_history_datetime DESC LIMIT 0,25;

+----+-------------+--------------+-------+---------------+-------------------------------+---------+------+---------+-------------+

| id | select_type | table        | type  | possible_keys | key                          | key_len | ref  | rows    | Extra      |

+----+-------------+--------------+-------+---------------+-------------------------------+---------+------+---------+-------------+

|  1 | SIMPLE      | user_history | index | NULL          | user_history_datetime_user_id | 12      | NULL | 5821234 | Using where |

+----+-------------+--------------+-------+---------------+-------------------------------+---------+------+---------+-------------+

1 row in set (0.00 sec)

 

[fenway]

Hmmm. still not ideal.

 

Can you try a few things?

 

1) switch to ASC -- just for now.

2) try switching the order of the keys in the index, then try ASC/DESC.

[danesc]

mysql> EXPLAIN SELECT * FROM user_history FORCE INDEX( user_history_datetime_user_id ) WHERE user_id=73 ORDER BY user_history_datetime ASC LIMIT 0,25;

+----+-------------+--------------+-------+---------------+-------------------------------+---------+------+---------+-------------+

| id | select_type | table        | type  | possible_keys | key                          | key_len | ref  | rows    | Extra      |

+----+-------------+--------------+-------+---------------+-------------------------------+---------+------+---------+-------------+

|  1 | SIMPLE      | user_history | index | NULL          | user_history_datetime_user_id | 12      | NULL | 5824343 | Using where |

+----+-------------+--------------+-------+---------------+-------------------------------+---------+------+---------+-------------+

1 row in set (0.00 sec)

 

 

mysql> EXPLAIN SELECT * FROM user_history WHERE user_id=73 ORDER BY user_history_datetime ASC LIMIT 0,25;

+----+-------------+--------------+------+---------------+---------+---------+-------+--------+-----------------------------+

| id | select_type | table        | type | possible_keys | key    | key_len | ref  | rows  | Extra                      |

+----+-------------+--------------+------+---------------+---------+---------+-------+--------+-----------------------------+

|  1 | SIMPLE      | user_history | ref  | user_id      | user_id | 4      | const | 303046 | Using where; Using filesort |

+----+-------------+--------------+------+---------------+---------+---------+-------+--------+-----------------------------+

1 row in set (0.00 sec)

 

now I'm about to change the order of the keys... redoing the index to be user_id, user_history_datetime

 

[danesc]

I think you solved it!!!

 

mysql>  EXPLAIN SELECT * FROM user_history WHERE user_id=73 ORDER BY user_history_datetime DESC LIMIT 0,25;

+----+-------------+--------------+------+---------------------------------------+-------------------------------+---------+-------+--------+-------------+

| id | select_type | table        | type | possible_keys                        | key                          | key_len | ref  | rows  | Extra      |

+----+-------------+--------------+------+---------------------------------------+-------------------------------+---------+-------+--------+-------------+

|  1 | SIMPLE      | user_history | ref  | user_id,user_id_user_history_datetime | user_id_user_history_datetime | 4      | const | 221780 | Using where |

+----+-------------+--------------+------+---------------------------------------+-------------------------------+---------+-------+--------+-------------+

1 row in set (0.01 sec)

 

 

AND ....

 

mysql>  EXPLAIN SELECT * FROM user_history WHERE user_id=73 AND user_history_datetime < date('2009-04-20') ORDER BY user_history_datetime DESC LIMIT 0,25;

+----+-------------+--------------+-------+-------------------------------------------------------------+-------------------------------+---------+------+--------+-------------+

| id | select_type | table        | type  | possible_keys                                              | key                          | key_len | ref  | rows  | Extra      |

+----+-------------+--------------+-------+-------------------------------------------------------------+-------------------------------+---------+------+--------+-------------+

|  1 | SIMPLE      | user_history | range | user_id,user_history_datetime,user_id_user_history_datetime | user_id_user_history_datetime | 12      | NULL | 221676 | Using where |

+----+-------------+--------------+-------+-------------------------------------------------------------+-------------------------------+---------+------+--------+-------------+

1 row in set (0.00 sec)

 

 

It is so much faster now.... we just took the average queries from over a second down to 0.0011

 

THANK YOU SO MUCH!!!!!!!!

[TheFilmGod]

I'm implementing a similar feature on the website. Can someone explain why a composite index (date, user_id) is more efficient than (user_id, date)? Wouldn't you want to first filter out all the records which do not relate to the user? - Followed by a date filter?

[fenway]

I think you solved it!!!

 

It is so much faster now.... we just took the average queries from over a second down to 0.0011

 

THANK YOU SO MUCH!!!!!!!!

Not a problem -- but make you that you remove ALL the other indexes -- including the one just on ( user_id ) as well any other covering ones we made in the process -- otherwise you'll have extra overhead for no reason.

 

I'm implementing a similar feature on the website. Can someone explain why a composite index (date, user_id) is more efficient than (user_id, date)? Wouldn't you want to first filter out all the records which do not relate to the user? - Followed by a date filter?

I'd like to confirm what the final index that actually worked was... hard to tell from the expalin.

[danesc]

fenway,

 

I have removed some of the indexes that I no longer need. As soon as I change the queries in other places that use the pre-existing indexes I will remove those as well. My goal is to make ALL the queries ONLY USE the one we created. THANK YOU so much again, this week our server has been performing like a charm thanks to this fix. Cheers.

[fenway]

No problem... so, for the record, which index worked???

[danesc]

user_id_user_history_datetime (user_id,user_history_datetime)

 

I've implemented this same technique of indexing in other tables and it has done miracles! Thank you so much once again ;-)