X
X
xbcaax2015-11-24 16:02:37
MySQL
xbcaax, 2015-11-24 16:02:37

Why does a simple query in mysql periodically slow down?

Periodically slows down a simple request. Why?
Log of similar requests over time:


11:50:21 - query time: 0.62003. pid:5028534
12:09:08 - query time:0.03472. pid:5028717
12:10:54 - query time:0.43697. pid:5028717
12:17:27 - query time:157.7397. pid:5028717
12:17:27 - query time:0.02519. pid:5028717
12:17:28 - query time:0.02408. pid:5028717
12:28:13 - query time:0.22632. pid:5028534
12:42:13 - query time:0.3948. pid:5028717

The request itself:
SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(action_history_timestamp) AS
diff_in_seconds, action_target_id 
FROM action_history WHERE action_id = 11 && user_id = '1111' && action_target_id ='1111' ORDER BY action_history_timestamp DESC LIMIT 1

The table has about 30 million records. There are indexes.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Anton B, 2015-11-24
@xbcaax

I think this is due to ORDER BY and LIMIT, because for these instructions to work, the result of the query is placed in a temporary table in memory. That is, I think you can increase some parameters in my.cnf - sort_buffer_size.

S
Semyon, 2015-11-24
@skamenetskiy

Perhaps other requests/processes on the server?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question