Slow SQL queries
-
Thank You for Your plugin.
I see in MySQL slow queries log queries from WP-o-Matic plugin.
1. In admin panel:
SELECT * FROM wp_wpo_log ORDER BY created_on DESC LIMIT 7;
Please – add index for
created_on
field:ALTER TABLE wp_wpo_log ADD INDEX created_on ( created_on DESC );
2. In admin panel:
SELECT Id ,title ,lastactive ,frequency FROM wp_wpo_campaign WHERE 1 = 1 AND UNIX_TIMESTAMP(lastactive)>0 ORDER BY lastactive DESC LIMIT 5;
Please, add indexes:
ALTER TABLE wp_wpo_campaign ADD INDEX active (active ASC) , ADD INDEX lastactive (lastactive ASC) , ADD INDEX created_on (created_on DESC) ;
and, please, redesign query, WHERE clause:
EXPLAIN SELECT Id ,title ,lastactive ,frequency FROM wp_wpo_campaign WHERE lastactive > FROM_UNIXTIME( 0 ) ORDER BY lastactive DESC LIMIT 5;
This version uses index, Your version doesn’t uses indexes.
3. And last two query:
SELECT id,title,lastactive,frequency FROM wp_wpo_campaign WHERE 1 = 1 AND active=1 ORDER BY UNIX_TIMESTAMP(lastactive)+frequency ASC LIMIT 5;
Please, don’t use calculate expression in filter – they don’t use indexes. Please, add field
nextactive
, and add index for this field, and use this field in filters (WHERE clause, ORDER BY…).Thank You for Your attention.
- The topic ‘Slow SQL queries’ is closed to new replies.