View the execution plan of mysql. The SQL statement is executed for 4 minutes, And the message_message contains 1000 million data. The execution plan of mysql is learned.
Select * from message_message where id in (select message_id
From message_message_tags where messagetag_id = 59885) and (category = 9 or category = 1)
Order by sum (like_count, favorite_count) desc limit 15,
If a developer is often at a loss or is directly handed over to the DBA to handle the problem, you are lucky to have a DBA,
However, if you do not have a DBA, the only possible solution is to view the execution plan.
(You can also directly use explain SQL to analyze ...): by default, Mysql's profiling is disabled. Therefore, you must first enable the profiling SQL code set profiling = "ON" mysql> show variables like "% profi % "; + ---------------------- + ------- + | Variable_name | Value | + ------------------------ + ------- + | profiling | ON | www.2cto.com show processlist; view the list of all processes currently running, the only thing we need in the process list is ID mysql> show processlist; + ---- + ------ + ---------------- + ----------- + --------- + ------ + ------- + ------------- + | Id | User | Host | db | Command | Time | State | Info | + ---- + ------ + ---------------- + ----------- + --------- + ------ + ------- + ------------- ----- + | 3 | root | localhost: 2196 | click_log | Query | 0 | NULL | show process list | + ---- + ------ + ---------------- + ----------- + --------- + ------ + ------- + ------------- mysql> show profile cpu, memory for query 3; + metric + ------------ + ---------- + ------------ + | Status | Duration | CPU_user | CPU_system | + metric + ------------ + ---------- + ------------ + | freeing items | 0.00001375 | NULL | logging slow query | 0.00001375 | NULL | cleaning up | 0.00000050 | NULL | + -------------------- + ------------ + ---------- + ------------ + show profiles Syntax: show profile [type [, type]...] [for query n] www.2cto.com [LIMIT row_count [OFFSET offset] type: ALL | block io | context switches | CPU | IPC | MEMORY | page faults | SOURCE | SWAPS author san_yun