Slow select query with join

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

March 14th, 2008, 11:19 AM

matt_fawcett

Contributing User

Join Date: Mar 2008

Posts: 34

Time spent in forums: 16 h 26 m 53 sec
Reputation Power: 2

Slow select query with join

Hi, I have built a web application that I can import a csv of users every week. There are about 155,000 and this grows each week. My application imports the csv and adds or updates a row in the database for each user, performs some selection criteria sql to send out emails and sms messages to some of the people and then updates a database table containing stats. In total the application takes a very long 24 hours to run and gets longer each week and I have been trying to optimize things with no luck. One of the queries that takes a long time and for which I cannot seen any reason for is one that gets data out to store in the statistics table. The query take 40 minutes and gets run several times. The number of times it has to run increments by 1 each week.

It is a simple join of 2 tables but takes ages to run. The query is:

Code:

SELECT COUNT(usr.id) as count 
FROM users usr 
JOIN contact_history_logs log 
ON usr.UserID = log.UserID
WHERE  usr.TotalCalls >= 1  
AND log.Camp_Code = 'N0@7email' 
AND log.Con_Date BETWEEN '2008-03-01'  AND '2008-03-07'

The users table has 34 columns with 155000 rows and the indexed fields are RegDate, TotalCalls, email, TotalCalls and UserID.

The contact history logs table has 15 columns with 68000 rows and the indexed fields are Con_Date, Camp_Code, TotalTime, and TotalCalls.

When I do an explain on the query it seems to ignore the index I have on UserID on the users table. The result of the explain is:

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	log 	ref 	Con_Date,Camp_Code 	Camp_Code 	14 	const 	21012 	Using where
1 	SIMPLE 	usr 	ALL 	UserID 	NULL 	NULL 	NULL 	157824 	Range checked for each record (index map: 0x20)

Has anyone got any ideas why the explain gives such weird results and anything I can do to make the query run faster as its not a massive database.

Thanks,
Matt

March 14th, 2008, 12:18 PM

ccalender

Contributing User

Join Date: Feb 2006

Posts: 182

Time spent in forums: 2 Days 1 h 36 m 15 sec
Reputation Power: 6

Please post the output of:

SHOW CREATE TABLE users;
SHOW CREATE TABLE contact_history_logs;

March 14th, 2008, 12:24 PM

matt_fawcett

Contributing User

Join Date: Mar 2008

Posts: 34

Time spent in forums: 16 h 26 m 53 sec
Reputation Power: 2

Code:

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `UserID` varchar(11) NOT NULL,
  `FirstName` varchar(70) default NULL,
  `LastName` varchar(70) default NULL,
  `email` varchar(70) default NULL,
  `TelNo` varchar(30) default NULL,
  `MyCountryName` varchar(30) default NULL,
  `TimeAdjust` int(4) NOT NULL,
  `NumType` varchar(20) default NULL,
  `Partner` varchar(30) default NULL,
  `Status` varchar(30) default NULL,
  `RegDate` datetime NOT NULL,
  `FirstPayDate` datetime default NULL,
  `LastPayDate` datetime default NULL,
  `FirstCallDate` datetime default NULL,
  `LastCallDate` datetime default NULL,
  `LastStatusDate` datetime default NULL,
  `TotalCalls` int(11) NOT NULL,
  `RebOutCount` int(11) NOT NULL,
  `RebOutTime` float NOT NULL,
  `RebInCount` int(11) NOT NULL,
  `RebInTime` float NOT NULL,
  `TotalTime` float NOT NULL,
  `Credit` float NOT NULL,
  `ConnectCount` int(11) NOT NULL,
  `FirstCallTo` varchar(30) default NULL,
  `Paid` float NOT NULL,
  `Seg` varchar(30) default NULL,
  `SubSeg` varchar(30) default NULL,
  `xRand` int(11) NOT NULL,
  `NoEmail` int(11) NOT NULL,
  `NoSMS` int(11) NOT NULL,
  `Dupe` int(11) NOT NULL default '0',
  `Date_Created` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `RegDate` (`RegDate`),
  KEY `TotalCalls` (`TotalCalls`),
  KEY `email` (`email`),
  KEY `TotalCalls_2` (`TotalCalls`),
  KEY `UserID` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and

Code:

CREATE TABLE `contact_history_logs` (
  `id` int(11) NOT NULL auto_increment,
  `Con_Date` datetime NOT NULL,
  `Con_Type` varchar(5) NOT NULL,
  `Camp_Code` varchar(12) NOT NULL,
  `sms_message` varchar(160) default NULL,
  `TotalCalls` int(5) default '0',
  `TotalTime` float default '0',
  `LastCallDate` datetime default NULL,
  `Paid` int(6) default '0',
  `LastPaidDate` datetime default NULL,
  `ConnectCount` int(5) default NULL,
  `Date_Created` datetime default NULL,
  `status` int(11) NOT NULL default '0',
  `UserID` int(11) NOT NULL,
  `MyCountryName` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `Con_Date` (`Con_Date`),
  KEY `Camp_Code` (`Camp_Code`),
  KEY `TotalTime` (`TotalTime`),
  KEY `TotalCalls` (`TotalCalls`)
) ENGINE=MyISAM AUTO_INCREMENT=68115 DEFAULT CHARSET=latin1

Thanks,
Matt

March 14th, 2008, 12:32 PM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,711

Time spent in forums: 2 Months 2 Weeks 6 Days 5 h 6 m 18 sec
Reputation Power: 259

I think your problem arises from the fact that you have

Quote:

`UserID` varchar(11)

and

Quote:

`UserID` int(11)

choose which datatype to use and use it in every table, you are forcing implicit casts.
Also, no difference between

Quote:

KEY `TotalCalls` (`TotalCalls`)

and

Quote:

KEY `TotalCalls_2` (`TotalCalls`)

remove one of the two as it's a waste of space and slows down inserts.
No need for an autoincrement id in your users table as the logical key seems to be the userid, at least place an unique key on it to insure data integrity

Comments on this post

matt_fawcett agrees: Thanks

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

March 14th, 2008, 12:36 PM

matt_fawcett

Contributing User

Join Date: Mar 2008

Posts: 34

Time spent in forums: 16 h 26 m 53 sec
Reputation Power: 2

Ah that great I shall give your recommendations a go.
Thanks for your help

March 14th, 2008, 01:02 PM

ccalender

Contributing User

Join Date: Feb 2006

Posts: 182

Time spent in forums: 2 Days 1 h 36 m 15 sec
Reputation Power: 6

Once you make those changes, please let us know how it works. Also, please post an updated EXPLAIN output, so we can see if it changed at all.

You may also benfit from some covering indexes, but I'd like to see the updated EXPLAIN output before commenting further.

March 15th, 2008, 09:49 AM

matt_fawcett

Contributing User

Join Date: Mar 2008

Posts: 34

Time spent in forums: 16 h 26 m 53 sec
Reputation Power: 2

Hi, Iv made the changes and it has fixed the problem, it executes in 0.67 sec now. The explain seems to be showing the correct thing now. It is

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	log 	ref 	Con_Date,Camp_Code 	Camp_Code 	257 	const 	26211 	Using where
1 	SIMPLE 	usr 	ref 	UserID,TotalCalls 	UserID 	4 	database.log.UserID 	1 	Using where

Thanks for your help.

Matt

Last edited by matt_fawcett : March 15th, 2008 at 09:56 AM. Reason: added number of seconds execution time

March 15th, 2008, 11:50 AM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 26 m 43 sec
Reputation Power: 891

from 40 minutes to 0.67 seconds, simply by fixing the datatype

whoa, eh

__________________
r937.com | rudy.ca

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: