MySQL thread hangs on Copying to tmp table

[Ravikumar Ramasamy]

Hi,

MySQL query execution hangs on "Copying to tmp table" state and it takes 18 seconds to execute the below query.

SELECT pd.PERIOD,COUNT(pd.PERIOD) AS cunt FROM PollingDetails pd,DeviceList dl ,SiteSetting si WHERE dl.NAME=pd.AGENT AND si.STARTID=dl.PROBEID AND si.NAME='ravikumar' GROUP BY pd.PERIOD

The second table PollingDetails's entire row scanned and didn't use the index column to filter out the join.

Query 1:

SELECT pd.PERIOD,COUNT(pd.PERIOD) AS cunt FROM PollingDetails pd,DeviceList dl ,SiteSetting si WHERE dl.NAME=pd.AGENT AND si.STARTID=dl.PROBEID AND si.NAME='ravikumar' GROUP BY pd.PERIOD

6 rows in set (17.55 sec)


mysql> explain SELECT pd.PERIOD,COUNT(pd.PERIOD) AS cunt FROM PollingDetails pd,DeviceList dl ,SiteSetting si WHERE dl.NAME=pd.AGENT AND si.STARTID=dl.PROBEID AND si.NAME='ravikumar' GROUP BY pd.PERIOD;
+----+-------------+-------+------+----------------------------------------+--------------------+---------+----------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+----------------------------------------+--------------------+---------+----------------------+--------+----------------------------------------------+
| 1 | SIMPLE | si | ref | PRIMARY,SiteSetting0_ndx | PRIMARY | 102 | const | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | pd | ALL | PollingDetails1_ndx | NULL | NULL | NULL | 754422 | |
| 1 | SIMPLE | dl | ref | PRIMARY,DeviceList0_ndx,probeid_idx | DeviceList0_ndx | 102 | ravitesting.pd.AGENT | 1 | Using where |
+----+-------------+-------+------+----------------------------------------+--------------------+---------+----------------------+--------+----------------------------------------------+
3 rows in set (0.00 sec)

The show processlist query returns


| 13 | root | localhost:64755 | ravitesting | Query | 5 | Copying to tmp table | SELECT pd.PERIOD,COUNT(pd.PERIOD) AS cunt FROM PollingDetails pd,DeviceList dl ,SiteSetting si WHERE dl.NAME |



Query 2:

mysql> select count(pd.PERIOD) from PollingDetails pd INNER JOIN (SELECT dl.NAME FROM SiteSetting si INNER JOIN DeviceList dl ON si.NAME='ravikumar' AND si.STARTID=dl.PROBEID ) dev ON dev.NAME=pd.AGENT GROUP BY pd.PERIOD

4 rows in set (1.33 sec)


mysql> explain select count(pd.PERIOD) from PollingDetails pd INNER JOIN (SELECT dl.NAME FROM SiteSetting si INNER JOIN DeviceList dl ON si.NAME='ravikumar' AND si.STARTID=dl.PROBEID ) dev ON dev.NAME=pd.AGENT GROUP BY pd.PERIOD;
+----+-------------+------------+------+---------------------------------+-----------------+---------+------------------
-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+------------+------+---------------------------------+-----------------+---------+------------------
-------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5093 | Using temporary; Using filesort |
| 1 | PRIMARY | pd | ref | PollingDetails1_ndx | PollingDetails1_ndx | 102 | modetails.NAME | 439 | |
| 2 | DERIVED | si | ref | PRIMARY,SiteSetting0_ndx | PRIMARY | 102 | | 1 | Using where |
| 2 | DERIVED | dl | ref | probeid_idx | probeid_idx | 9 | ravitesting.si.STARTID | 4761 | Using where |
+----+-------------+------------+------+---------------------------------+-----------------+---------+------------------
-------+------+---------------------------------+
4 rows in set (0.10 sec)


data row count details

DeviceList -- 51369
PollingDetails -- 754422
SiteSetting -- 7


create table DeviceList(
"NAME" varchar(100) NOT NULL,
"DISPLAYNAME" varchar(255) NOT NULL,
"TYPE" varchar(100),
"POLLINTERVAL" INTEGER,
"STATUSCHANGE" varchar(25),
"STATUSUPDATE" varchar(25),
"OWNER" varchar(25) NOT NULL,
"STATUSPOLLENABLED" varchar(10),
"STATE" INTEGER,
"PROBEID" BIGINT,
PRIMARY KEY ("NAME","OWNER"),
index DeviceList0_ndx("NAME"),
index DeviceList1_ndx ("OWNER"),
index DeviceList3_ndx ("PROBEID"))


create table SiteSetting (
"NAME" VARCHAR(100) NOT NULL,
"OWNER" VARCHAR(25) NOT NULL,
"TIMEDIFF" BIGINT,
"TIMEZONE" VARCHAR(50),
"LASTUPDATETIME" BIGINT,
"STARTID" BIGINT default NULL,
PRIMARY KEY ("NAME","OWNER"),
INDEX SiteSetting0_ndx("NAME"),
INDEX SiteSetting1_ndx("OWNER"))

create table PollingDetails (
"NAME" varchar(250) NOT NULL ,
"ID" BIGINT NOT NULL ,
"AGENT" varchar(100) NOT NULL ,
"COMMUNITY" varchar(100) NOT NULL ,
"PERIOD" INTEGER NOT NULL,
"SAVEABSOLUTES" varchar(10),
"PORT" INTEGER,
"LASTTIMEVALUE" BIGINT ,
"TIMEVAL" BIGINT NOT NULL ,
"OWNER" varchar(25) NOT NULL,
"PARENTOBJ" varchar(100),
"PROTOCOL" varchar(50),
"CURRENTSAVECOUNT" INTEGER,
"SNMPVERSION" varchar(10),
PRIMARY KEY ("ID"),
index PollingDetails0_ndx ( "NAME"),
index PollingDetails1_ndx ( "AGENT"),
index PollingDetails2_ndx ( "ID"),
index PollingDetails3_ndx ( "PERIOD"),
index PollingDetails4_ndx ( "TIMEVAL"),
index PollingDetails5_ndx ( "PARENTOBJ"))


Is it possible to optimize the query? .. If I change the query with subquery, it will take 2 seconds to execute. Why first query didn't use the index column to join the table.

Looking forward your response.

Thanks
R.Ravikumar