MySQL Forums
Forum List  »  Optimizer & Parser

want to make this query faster, but not sure how
Posted by: Luc Foisy
Date: January 19, 2012 09:55AM

SELECT `webusers`.`webuserID` AS `ID` , `webusers`.`webuserLastName` AS `Last Name` , `webusers`.`webuserFirstName` AS `First Name` , `webusers`.`webuserEmail` AS `Email` , `company`.`name` AS `Company` , CASE `webusers`.`status` WHEN '0' THEN 'Deleted' WHEN '1000' THEN 'Inactive' WHEN '2000' THEN 'Pending' WHEN '3000' THEN 'Active' END AS `Web Access` , CASE `webusers`.`webuserLevel` WHEN '0' THEN 'None' WHEN '1000' THEN 'Public' WHEN '1001' THEN 'Gold' WHEN '1002' THEN 'Member' WHEN '1003' THEN 'Admin' WHEN '1004' THEN 'Executive' END AS `Access Level` , `membership`.`name` AS `Membership` , `member`.`registeredDate` AS `Registered Date` , CASE `member`.`status` WHEN '1' THEN 'Active' WHEN '2' THEN 'Renew' WHEN '3' THEN 'Late' WHEN '4' THEN 'Inactive' END AS `Membership Status` FROM `webusers` LEFT JOIN `company_webuser` ON `webusers`.`webuserID` = `company_webuser`.`webuserID` LEFT JOIN `company` ON `company_webuser`.`companyID` = `company`.`ID` LEFT JOIN `member` ON `webusers`.`webuserID` = `member`.`webUserID` LEFT JOIN `membership` ON `member`.`membershipID` = `membership`.`ID` AND `membership`.`i8nID` = '1' WHERE `webusers`.`status` != '0' ORDER BY `Last Name` ASC LIMIT 100;
3.44 seconds

explain
+----+-------------+-----------------+--------+---------------+---------+---------+--------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+--------+---------------+---------+---------+--------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | webusers | range | status | status | 4 | NULL | 1544 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | company_webuser | index | NULL | PRIMARY | 12 | NULL | 264 | Using index |
| 1 | SIMPLE | company | eq_ref | PRIMARY | PRIMARY | 4 | icsa.company_webuser.companyID | 1 | |
| 1 | SIMPLE | member | ALL | NULL | NULL | NULL | NULL | 1545 | |
| 1 | SIMPLE | membership | eq_ref | PRIMARY | PRIMARY | 8 | icsa.member.membershipID,const | 1 | |
+----+-------------+-----------------+--------+---------------+---------+---------+--------------------------------+------+----------------------------------------------+

CREATE TABLE `webusers` (
`webuserID` int(11) NOT NULL auto_increment,
`i8nID` int(11) NOT NULL default '1',
`createdDate` datetime NOT NULL default '0000-00-00 00:00:00',
`modifiedDate` datetime default NULL,
`webuserFirstName` varchar(255) NOT NULL default '',
`webuserLastName` varchar(255) NOT NULL default '',
`webuserUsername` varchar(255) NOT NULL default '',
`webuserTitle` varchar(64) NOT NULL default '' COMMENT 'depricated as of 4.0.46, in company_webuser table',
`webuserCompany` varchar(64) NOT NULL default '' COMMENT 'depricated as of 4.0.46, in company table',
`webuserPassword` varchar(64) NOT NULL default '',
`webuserEmail` varchar(255) NOT NULL default '',
`webuserLevel` int(11) NOT NULL default '1000',
`webuserAltField` varchar(255) default NULL,
`IsPublic` tinyint(4) NOT NULL default '0',
`webuserPhotoMediaID` smallint(4) NOT NULL default '0',
`IsPublicName` tinyint(4) NOT NULL default '0',
`IsPublicEmail` tinyint(4) NOT NULL default '0',
`IsPublicAddress` tinyint(4) NOT NULL default '0',
`IsPublicPhoto` tinyint(4) NOT NULL default '0',
`IsPublicStreet` tinyint(4) NOT NULL default '0',
`IsPublicCity` tinyint(4) NOT NULL default '0',
`IsPublicProvince` tinyint(4) NOT NULL default '0',
`IsPublicCountry` tinyint(4) NOT NULL default '0',
`IsPublicPostalCode` tinyint(4) NOT NULL default '0',
`IsPublicPhone` tinyint(4) NOT NULL default '0',
`IsPublicFax` tinyint(4) NOT NULL default '0',
`HTMLEmailPreferred` tinyint(4) NOT NULL default '0',
`status` int(11) NOT NULL default '0',
`ipaddress` int(10) unsigned NOT NULL default '0' COMMENT 'using INET_ATON and INET_NTOA',
PRIMARY KEY (`webuserID`),
KEY `status` (`status`)

CREATE TABLE `company_webuser` (
`ID` int(11) NOT NULL auto_increment,
`createdDate` datetime default NULL,
`modifiedDate` datetime default NULL,
`companyID` int(11) NOT NULL default '0',
`webuserID` int(11) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`primaryWebUser` tinyint(4) NOT NULL default '0',
`canEditCompany` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`ID`,`companyID`,`webuserID`),
KEY `primaryWebUser` (`primaryWebUser`),
KEY `canEditCompany` (`canEditCompany`)

CREATE TABLE `company` (
`ID` int(11) NOT NULL auto_increment,
`createdDate` datetime default NULL,
`modifiedDate` datetime default NULL,
`name` varchar(255) NOT NULL default '',
`description` text,
`URL` varchar(255) NOT NULL default '',
PRIMARY KEY (`ID`)

CREATE TABLE `member` (
`ID` int(11) NOT NULL auto_increment,
`createdDate` datetime default NULL,
`modifiedDate` datetime default NULL,
`membershipID` int(11) NOT NULL default '0',
`webUserID` int(11) NOT NULL default '0',
`registeredDate` datetime default NULL,
`status` int(11) NOT NULL default '0',
`corporateIdentity` int(11) NOT NULL default '1',
PRIMARY KEY (`ID`,`membershipID`,`webUserID`)

CREATE TABLE `membership` (
`ID` int(11) NOT NULL auto_increment,
`createdDate` datetime default NULL,
`modifiedDate` datetime default NULL,
`i8nID` int(11) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`description` text,
`accessLevel` int(11) NOT NULL default '0',
`cost` decimal(30,10) NOT NULL default '0.0000000000',
`newCost` decimal(30,10) NOT NULL default '0.0000000000',
`lateCost` decimal(30,10) NOT NULL default '0.0000000000',
`hidden` tinyint(4) default '0',
`corporate` tinyint(4) NOT NULL default '0',
`maxMembers` int(11) NOT NULL default '0',
`expiresDate` datetime default NULL,
`duration` int(11) NOT NULL default '0',
`durationUnit` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`,`i8nID`)

Options: ReplyQuote


Subject
Views
Written By
Posted
want to make this query faster, but not sure how
2024
January 19, 2012 09:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.