[FIXED] Which Files In My Application that Make Slow Mysql Query Log Entries

Issue

I have been struggling for 2 days for this problem and have not found a solution.

I log mysql slow query with 10 seconds period. In the log file there are a huge entries with this statement:

# Thread_id: 222244  Schema: user  QC_hit: No
# Query_time: 51.019708  Lock_time: 0.000119  Rows_sent: 1  Rows_examined: 13295012
# Rows_affected: 0
SET timestamp=1559388099;
SELECT (COUNT(*)) AS `count` 
FROM statistics Statistics WHERE (id >= 1 AND ad_type <> 3);

Which is associated with a surge in apache requests. The query_time is up to one minute or more completed and causes my server to be overloaded. The problem is I can't find which file in my php script that causes the slow queries.

Is there any command line to find the associated file. Or any fast way?

CREATE TABLE statistics` (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
    ... , 
    PRIMARY KEY (id), 
    KEY idx_uid (uid), 
    KEY idx_ip (ip), 
    KEY idx_cnid_uid (cid,uid), 
    KEY idx_rlid (rid), 
    KEY idx_created_uid (created,uid)
) ENGINE=InnoDB AUTO_INCREMENT=34015 DEFAULT CHARSET=utf8

Solution

If there is no reason for checking id >= 1, get rid of it -- it inhibits certain optimizations.

Then add INDEX(ad_type)

Still, I see no good reason for COUNT(*) on a 34K-row(?) table to take 51 seconds. Something else must be involved.

Answered By - Rick James