Conversation

Notices

1. Georgi Georgiev

   Question: How to Debug MySQL slow queries from command line?

   Question

   How to get more information about MySQL query which is stoning the SQL server?1 answerThis question is closed.

   Tuesday, 10-Feb-15 12:11:06 UTC from web
   • Georgi Georgiev

     answered "How to Debug MySQL slow queries from command line?"

     Answer

     I've been struggling with my wordpress installation which is becoming more and more slow with time. I've used a WP plugin to track down which plugin is putting most load on MySQL server and I come across a problematic query. Thus I needed to now debug what is the issues behind the query. Here is example of a LEFT JOIN slow query - http://pastebin.pc-freak.net/view/fe0641a6 To get basic debugging on SQL table structure: 1. Run EXPLAIN SELECT * ... (rest of long query - instead of SELECT * just paste your query); - http://pastebin.pc-freak.net/view/21d401d7 2. Use USE Database_Name; SHOW CREATE TABLE of the table which is queried to check its structure, Often adding Index to a table raw improves performance: - Here is example http://pastebin.pc-freak.net/view/fd9c6835

     Tuesday, 10-Feb-15 12:26:45 UTC
   • Georgi Georgiev

     Other way to analyze slow queries is to enable enble slow query log from /etc/mysql/my.cnf add to config: slow-query-log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes And then use mysqldumpslow command to analyze the log

     Tuesday, 10-Feb-15 12:30:37 UTC
   • Georgi Georgiev

     To add INDEX to a TABLE COLUMN use query: ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

     Wednesday, 11-Feb-15 15:41:36 UTC
   • Georgi Georgiev

     A very good article on how to diagnose a slow query is here - http://s.pc-freak.net/url/295

     Wednesday, 11-Feb-15 16:00:34 UTC