CODEWITHSUNDEEP
mysql
Adhip R
Adhip R

Reputation: 23

Why do this query takes more than 180 secs to run?

CREATE TABLE `tvnotif` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pingId` int(11) DEFAULT NULL,
  `token` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `summary` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `startTime` int(11) DEFAULT NULL,
  `endTime` int(11) DEFAULT NULL,
  `processed` int(1) DEFAULT '0',
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `processedIndex` (`processed`),
  KEY `summaryIndex` (`summary`),
  KEY `tokenIndex` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=18297898 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



CREATE TABLE `vv_us` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `athleteid` int(11) DEFAULT NULL,
  `token` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `secret` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `active` int(1) DEFAULT '1',
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` varchar(45) COLLATE utf8_bin DEFAULT 'mc',
  `step` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `host` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `server` mediumblob,
  `tempcreds` mediumblob,
  PRIMARY KEY (`id`),
  KEY `activeIndex` (`active`),
  KEY `typeIndex` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=33888 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I am running a query which is mostly based on the table tvnotif which has at least 2 million rows of data in it, all the other tables are having less amount only. I added the index for the tables before that the query took 20 minutes to run and now its taking 160 secs.

EXPLAIN SELECT tvu.secret,COALESCE(php_timezone,"America/Los_Angeles") AS userTz,tn.*,tvu.athleteid,tvu.type FROM tvnotif AS tn 
            LEFT JOIN vv_us AS tvu ON  tvu.token = tn.token
            LEFT JOIN tbl_ath_pro AS tap ON tap.athleteid = tvu.athleteid
            LEFT JOIN timezones AS tz ON tz.tz_id = tap.tz_id
            WHERE tvu.active = 1 AND tn.summary = 'dailies' AND tn.processed = 0
            LIMIT 300

enter image description here

Upvotes: 0

Views: 66

Answers (1)

DRapp
DRapp

Reputation: 48179

The problem is probably your indexes... You have indexes on each field individually. What you need is a composite index on ALL 3 parts as a single index. Without, it can't pick the best one as you have 3 parts of the where clause.

Build a SINGLE index on ( processed, summary, token )

This way the query can jump directly to the processed records, directly to the summary value and then get those records and be done.

Additionally, your VV_US table should have an index on ( token, active ) so the join will be optimized on BOTH parts.

Upvotes: 2

Related Questions