danesc

I'm looking into using mysql-proxy ... any one uses this? thoughts? Things to lookout for? Thanks in advance

Guys, I have two MySQL servers 5.0.77-log on different machines. One is the "master" and the other one is the "slave" with a replication happening real-time. Here is my question: How do I configure my PHP scripts to use the Master for INSERT, UPDATE and the Slave for all SELECT statements? My current configuration is setup to do all MySQL interactions with one server (Master) but now I want to separate the two. Here is my current configuration... please help out with what the new configuration would be: $database = "some_db_name"; $user = "some_username_here"; $pass = "some_password_here"; $host = "xx.xx.xx.xx"; $connection = mysql_connect( $host, $user, $pass ) or failed_connection( mysql_error() ); mysql_select_db( $database ) or failed_connection( mysql_error() ); Thank you in advance!

If I do a SELECT with the same WHERE here is what I get mysql> SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1; -------------- SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1 -------------- +-------------------+-------------+-------------------+----------+-----------------------+ | playlist_media_id | playlist_id | playlist_position | media_id | playlist_media_status | +-------------------+-------------+-------------------+----------+-----------------------+ | 464323 | 6954 | 5939 | 5722053 | 0 | +-------------------+-------------+-------------------+----------+-----------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1; -------------- EXPLAIN SELECT * FROM playlist_media WHERE playlist_media_id=464323 LIMIT 1 -------------- +----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | playlist_media | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) Thoughts?? Thanks...

Why would this update be taking so long to execute? # Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE playlist_media SET playlist_position=5939 WHERE playlist_media_id=464323 LIMIT 1; MySQL version: 5.0.77-log Source distribution mysql> DESCRIBE playlist_media; -------------- DESCRIBE playlist_media -------------- +-----------------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+------------+------+-----+---------+----------------+ | playlist_media_id | int(11) | NO | PRI | NULL | auto_increment | | playlist_id | int(11) | NO | MUL | NULL | | | playlist_position | int(11) | NO | | NULL | | | media_id | int(11) | NO | MUL | NULL | | | playlist_media_status | tinyint(4) | NO | MUL | NULL | | +-----------------------+------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> SHOW INDEX FROM playlist_media; -------------- SHOW INDEX FROM playlist_media -------------- +----------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+ | playlist_media | 0 | PRIMARY | 1 | playlist_media_id | A | 520018 | NULL | NULL | | BTREE | | | playlist_media | 1 | playlist_id | 1 | playlist_id | A | 4905 | NULL | NULL | | BTREE | | | playlist_media | 1 | media_id | 1 | media_id | A | 260009 | NULL | NULL | | BTREE | | | playlist_media | 1 | playlist_media_status | 1 | playlist_media_status | A | 4 | NULL | NULL | | BTREE | | +----------------+------------+-----------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+ 4 rows in set (0.01 sec) Total records in the table 520,018 Thank you so much in advance...

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!

Nice one Fenway... however it doesn't resolve my issue ;-) I'm using Server version: 5.0.77-log Source distribution

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.

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

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 ;-)

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 ;-)

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.

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!!!!!!!!

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

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)

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 ;-)