danesc Posted April 28, 2009 Share Posted April 28, 2009 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 ;-) Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/ Share on other sites More sharing options...
premiso Posted April 28, 2009 Share Posted April 28, 2009 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. ??? Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821106 Share on other sites More sharing options...
fenway Posted April 28, 2009 Share Posted April 28, 2009 Yes, please remove those, post your table structure, and show us the explain output again. Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821231 Share on other sites More sharing options...
danesc Posted April 28, 2009 Author Share Posted April 28, 2009 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 Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821324 Share on other sites More sharing options...
fenway Posted April 28, 2009 Share Posted April 28, 2009 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. Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821334 Share on other sites More sharing options...
danesc Posted April 28, 2009 Author Share Posted April 28, 2009 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. Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821351 Share on other sites More sharing options...
fenway Posted April 28, 2009 Share Posted April 28, 2009 Well, when you forced the index, you didn't have a filesort ;-) Which version of mysql? Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821359 Share on other sites More sharing options...
danesc Posted April 28, 2009 Author Share Posted April 28, 2009 Nice one Fenway... however it doesn't resolve my issue ;-) I'm using Server version: 5.0.77-log Source distribution Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821365 Share on other sites More sharing options...
fenway Posted April 29, 2009 Share Posted April 29, 2009 But you said "Using FORCE makes the query takes about 0.0084 sec VS 0.0129 sec"... how is that "slow? Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-821758 Share on other sites More sharing options...
danesc Posted April 29, 2009 Author Share Posted April 29, 2009 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! Link to comment https://forums.phpfreaks.com/topic/155920-solved-slow-query-not-really-but-added-to-slow_querylog/#findComment-822053 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.