I have this query that is running slow (16 seconds), it only has 44085 records in the biggest table. Any suggestions or anything that sticks out?
thanks for any help
SELECT u.`vid`, u.`userID`, u.`localConID`, u.`lastran`, u.`laststatus`,
u.`lastmessage`, u.`active`
,u.`autorundaily`, u.`autorunmonthly`, u.`fileslocation`
,c.`conid`, c.`fname`, c.`lname`, c.`homephone`, c.`cellphone` , c.`email` ,
DATE_FORMAT(u.`lastran`,'%d/%m/%y %k:%i') lastranFormatted, u.`retrys`
FROM virtual_alerts_users u
LEFT JOIN virtual_alerts_cons c ON c.referid = u.localConID
WHERE u.userID = 9581
When i do an explain i get::
id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
1 | SIMPLE | u | ALL | Index 3 | null | null | null | 459 | Using where
1 | SIMPLE | c | ALL | null | null | null | null | 44085 |
The tables look like::
CREATE TABLE `virtual_alerts_users` (
`vid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`userID` INT(11) NOT NULL DEFAULT '0',
`localConID` VARCHAR(10) NULL DEFAULT NULL,
`encrpytPW` VARCHAR(100) NULL DEFAULT NULL,
`lastran` TIMESTAMP NULL DEFAULT NULL,
`laststatus` INT(11) NULL DEFAULT NULL,
`lastmessage` TEXT NULL,
`active` TINYINT(4) NOT NULL DEFAULT '0',
`autorundaily` TINYINT(4) NOT NULL DEFAULT '0',
`autorunmonthly` TINYINT(4) NOT NULL DEFAULT '0',
`fileslocation` VARCHAR(512) NULL DEFAULT NULL,
`retrys` TINYINT(4) NULL DEFAULT '0',
PRIMARY KEY (`vid`),
UNIQUE INDEX `Index 2` (`localConID`),
INDEX `Index 3` (`userID`)
)
-
CREATE TABLE `virtual_alerts_cons` (
`conid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`userID` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`vid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`fname` VARCHAR(50) NULL DEFAULT NULL,
`lname` VARCHAR(50) NULL DEFAULT NULL,
`referid` VARCHAR(10) NULL DEFAULT NULL,
`level` VARCHAR(2) NULL DEFAULT NULL,
`status` VARCHAR(2) NULL DEFAULT NULL,
`lang` VARCHAR(15) NULL DEFAULT NULL,
`homephone` VARCHAR(15) NULL DEFAULT NULL,
`cellphone` VARCHAR(15) NULL DEFAULT NULL,
`address` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`birthday_mon` TINYINT(4) NULL DEFAULT '0',
`birthday_day` TINYINT(4) NULL DEFAULT '0',
`anv_mon` TINYINT(4) NULL DEFAULT '0',
`anv_day` TINYINT(4) NULL DEFAULT '0',
`anv_cnt` TINYINT(4) NULL DEFAULT '0',
`lasthash` BIGINT(20) NULL DEFAULT '0',
`lastupdated` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`conid`),
UNIQUE INDEX `Index 3` (`userID`, `referid`),
INDEX `Index 2` (`userID`),
INDEX `Index 4` (`vid`)
)
You have no index on
referid
in virtual_alerts_cons
, but you do have a combined index on userID
and referid
.
To force MySQL to use that, change your join condition to:
LEFT JOIN
virtual_alerts_cons c
ON
c.referid = u.localConID
AND
c.userId = u.userID
Alternatively, you could create an additional index on
referid
.
0 comments:
Post a Comment