Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have noticed that my queries are getting slower. Some update queries take about 48-50 seconds.

Here's how it looks:

enter image description here

[root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log
# Time: 131113 17:09:48
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 61.013294  Lock_time: 0.000409 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380588;
UPDATE b SET nf=0, lt=0, ud=1384380501, a='Trademarks' WHERE pid=36019263 AND l=3780235;
# Time: 131113 17:09:49
# User@Host: J[J] @  [10.xxx.xxx.35]
# Query_time: 18.145112  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1384380589;
SELECT id FROM b WHERE pid=62921670 AND l=4808881;
[root@db-1 ~]#

Here are the tables that are working together:

mysql> explain b;

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(30)      | NO   | PRI | NULL    | auto_increment |
| l           | int(30)      | NO   | MUL | NULL    |                |
| pid         | int(30)      | NO   | MUL | NULL    |                |
| nf          | int(1)       | NO   |     | NULL    |                |
| lt          | int(1)       | NO   |     | NULL    |                |
| fd          | int(11)      | NO   |     | NULL    |                |
| ud          | int(11)      | NO   |     | NULL    |                |
| le          | int(1)       | NO   | MUL | NULL    |                |
| a           | varchar(150) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> explain l;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(30)      | NO   | PRI | NULL    | auto_increment |
| u          | varchar(255) | NO   | UNI | NULL    |                |
| ld         | int(11)      | YES  | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Here another explain:

mysql> EXPLAIN SELECT id FROM b WHERE pid=8857932 AND l=3971963;
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table     | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                                     |
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | b         | index_merge | pid,l          | pid,l          | 4,4     | NULL |    1 | Using intersect(pid,l); Using where; Using index          |
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT nf=0, lt=0, ud=1384380501, a='Trademarks' FROM b WHERE pid=8857932 AND l=3971963;
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
| id | select_type | table     | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                        |
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | b         | index_merge | pid,l          | pid,l          | 4,4     | NULL |    1 | Using intersect(pid,l); Using where          |
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+


mysql> EXPLAIN UPDATE b SET nf=0, lt=0, ud=1384474869, a='Order Status' WHERE pid=8858794 AND l=4997886;
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
| id | select_type | table     | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                        |
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | b         | index_merge | pid,l          | pid,l          | 4,4     | NULL |    1 | Using intersect(pid,l); Using where          |
+----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
1 row in set (0.08 sec)

EDIT (15.11.2013):

I upgraded to Percona's 5.6 server:

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.6.14-56-log |
+---------------+
1 row in set (0.00 sec)
share|improve this question
    
Did you miss the 400 million rows (84Gb of data) part, or do you think it should be able to do that in a millisecond? –  Phil Nov 14 '13 at 22:18
    
It would be better to get it under 10 seconds at least. Since this database will grow a lot.. I'm trying to figure out how I could optimize the speed. Maybe I should something else besides MySQL database? –  Jason Nov 14 '13 at 22:20
    
Having said that, may just need an index to make the update faster if it's having to tablescan due to a lack of index & only a couple of rows are being updated. You'll need to edit your answer with EXPLAINs to get further help –  Phil Nov 14 '13 at 22:21
    
I updated the post. –  Jason Nov 14 '13 at 22:27
2  
The efficiency would be improved with a composite index on (pid,l). It would use that index instead of what it does now, taking the 2 separate indexes and then their intersection. –  ypercube Nov 15 '13 at 6:37
show 8 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.