[SOLVED] Slow query? - not really but added to slow_query.log

[danesc]

Why would the following SQL statement be added to the slow query log?

 

# Time: 090427 19:52:42

# Query_time: 0  Lock_time: 0  Rows_sent: 20  Rows_examined: 53

SELECT * FROM playlist FORCE INDEX(playlist_last_play_date) JOIN user on user.user_id = playlist.user_id WHERE playlist.playlist_status=0 AND playlist.playlist_play_permissions=0 AND playlist.playlist_song_count<>0 AND playlist.playlist_play_count<>0 AND user.user_avatar=1 ORDER BY playlist.playlist_last_play_date DESC LIMIT 20;

 

 

mysql> EXPLAIN SELECT * FROM playlist FORCE INDEX(playlist_last_play_date) JOIN user on user.user_id = playlist.user_id WHERE playlist.playlist_status=0 AND playlist.playlist_play_permissions=0 AND playlist.playlist_song_count<>0 AND playlist.playlist_play_count<>0 AND user.user_avatar=1 ORDER BY playlist.playlist_last_play_date DESC LIMIT 20;

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

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

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

|  1 | SIMPLE      | playlist | index  | NULL          | playlist_last_play_date | 8      | NULL                            | 7110 | Using where |

|  1 | SIMPLE      | user    | eq_ref | PRIMARY      | PRIMARY                | 4      | maestro_online.playlist.user_id |    1 | Using where |

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

2 rows in set (0.00 sec)

 

 

And here are the settings I have for the /etc/my.cnf file

 

long_query_time = 2

log-queries-not-using-indexes

 

 

BTW, ALL tables are MyISAM

 

Thank you in advance ;-)

 

 

 

 

 

[premiso]

My bet is the FORCE INDEX portion. I have never used it (or seen it used) but why not just have that field in the table structure be indexed instead of putting it in the query. ???

[fenway]

Yes, please remove those, post your table structure, and show us the explain output again.

[danesc]

Question: Why would the FORCE INDEX trigger a log into the slow query?

 

to answer the questions:

 

mysql> describe playlist;

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

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

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

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

| playlist_key                        | varchar(40)  | NO  | MUL | NULL    |                |

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

| playlist_name                        | varchar(255) | NO  | MUL | NULL    |                |

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

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

| playlist_last_play_date              | datetime    | NO  | MUL | NULL    |                |

| playlist_create_date                | datetime    | NO  | MUL | NULL    |                |

| playlist_update_date                | datetime    | NO  | MUL | NULL    |                |

| playlist_content_database            | int(11)      | NO  |    | 0      |                |

| playlist_status                      | smallint(6)  | NO  | MUL | NULL    |                |

| playlist_itunes_playlistID          | int(11)      | NO  |    | NULL    |                |

| playlist_itunes_playlistPersistentID | varchar(16)  | NO  |    | NULL    |                |

| playlist_page_views                  | int(11)      | NO  | MUL | 0      |                |

| playlist_last_page_view              | datetime    | NO  |    | NULL    |                |

| playlist_play_permissions            | tinyint(4)  | NO  | MUL | NULL    |                |

| playlist_view_permissions            | tinyint(4)  | NO  | MUL | NULL    |                |

| playlist_avatar                      | tinyint(1)  | NO  |    | NULL    |                |

| playlist_avatar_ext                  | varchar(10)  | NO  |    | NULL    |                |

| playlist_description                | text        | NO  |    | NULL    |                |

| playlist_availability_percent        | int(2)      | YES  | MUL | 0      |                |

| playlist_cached_count                | int(11)      | YES  | MUL | 0      |                |

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

22 rows in set (0.00 sec)

 

 

 

the index:

 

mysql> show index from playlist;

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

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

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

| playlist |          0 | PRIMARY                  |            1 | playlist_id                  | A        |        7135 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | user_id                  |            1 | user_id                      | A        |        1783 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_key              |            1 | playlist_key                  | A        |        7135 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_status          |            1 | playlist_status              | A        |          1 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_update_date      |            1 | playlist_update_date          | A        |        264 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_availability    |            1 | playlist_availability_percent | A        |          9 |    NULL | NULL  | YES  | BTREE      |        |

| playlist |          1 | playlist_cached          |            1 | playlist_cached_count        | A        |        209 |    NULL | NULL  | YES  | BTREE      |        |

| playlist |          1 | playlist_last_play_date  |            1 | playlist_last_play_date      | A        |        3567 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_song_count      |            1 | playlist_song_count          | A        |        339 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_play_count      |            1 | playlist_play_count          | A        |        594 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_play_permissions |            1 | playlist_play_permissions    | A        |          3 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_view_permissions |            1 | playlist_view_permissions    | A        |          1 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_create_date      |            1 | playlist_create_date          | A        |        7135 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_page_views      |            1 | playlist_page_views          | A        |        648 |    NULL | NULL  |      | BTREE      |        |

| playlist |          1 | playlist_name            |            1 | playlist_name                | A        |        3567 |    NULL | NULL  |      | BTREE      |        |

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

15 rows in set (0.00 sec)

 

 

 

The reason why we need so many indexes is because of all the different types of sorting we need to do to allow users to navigate their library.....

 

Here is WITHOUT the FORCE:

 

mysql> EXPLAIN SELECT * FROM playlist  JOIN user on user.user_id = playlist.user_id WHERE playlist.playlist_status=0 AND playlist.playlist_play_permissions=0 AND playlist.playlist_song_count<>0 AND playlist.playlist_play_count<>0 AND user.user_avatar=1 ORDER BY playlist.playlist_last_play_date DESC LIMIT 20\G

*************************** 1. row ***************************

          id: 1

  select_type: SIMPLE

        table: playlist

        type: range

possible_keys: user_id,playlist_status,playlist_song_count,playlist_play_count,playlist_play_permissions

          key: playlist_play_count

      key_len: 4

          ref: NULL

        rows: 4288

        Extra: Using where; Using filesort

*************************** 2. row ***************************

          id: 1

  select_type: SIMPLE

        table: user

        type: eq_ref

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 4

          ref: maestro_online.playlist.user_id

        rows: 1

        Extra: Using where

2 rows in set (0.00 sec)

 

 

 

Using FORCE makes the query takes about 0.0084 sec VS 0.0129 sec

[fenway]

well, that's due to the filesort issue....

 

but not sure how it could ever be any faster... you're asking for "all" rows at least one of the tables.

[danesc]

I think you're right Fenway.... by using FORCE it says it is using INDEX, however it is scanning all rows in the table. How do I get rid of the filesort in this instance? Is having "Filesort" a bad thing in general? Can you FORCE multiple indexes to limit the amount of rows scanned?

 

Thank you for all your help.

[fenway]

Well, when you forced the index, you didn't have a filesort ;-)

 

Which version of mysql?

[danesc]

Nice one Fenway... however it doesn't resolve my issue ;-)

 

I'm using Server version: 5.0.77-log Source distribution

 

[fenway]

But you said "Using FORCE makes the query takes about 0.0084 sec VS 0.0129 sec"... how is that "slow?

[danesc]

I agree it's not "SLOW", however when you multiply that times millions of queries it adds up... I ended up removing the FORCE to make sure it used index and only scanned the necessary rows. Thank you to all of you for your help.

 

This forum has been extremely helpful. Cheers!