Slow select query with join

Dev Shed Forums Sponsor:

March 14th, 2008, 11:19 AM

matt_fawcett

Contributing User

Join Date: Mar 2008

Posts: 37

Time spent in forums: 17 h 41 sec
Reputation Power: 3

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: 279

Time spent in forums: 3 Days 1 h 34 m 50 sec
Reputation Power: 36

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: 37

Time spent in forums: 17 h 41 sec
Reputation Power: 3

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 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,257

Time spent in forums: 3 Months 2 Days 11 h 54 m 38 sec
Reputation Power: 524

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
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (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
Random data (with a bias)

March 14th, 2008, 12:36 PM

matt_fawcett

Contributing User

Join Date: Mar 2008

Posts: 37

Time spent in forums: 17 h 41 sec
Reputation Power: 3

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: 279

Time spent in forums: 3 Days 1 h 34 m 50 sec
Reputation Power: 36

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: 37

Time spent in forums: 17 h 41 sec
Reputation Power: 3

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

Join Date: Feb 2003

Location: Toronto Canada

Posts: 19,829

Time spent in forums: 2 Months 6 Days 9 h 50 m 18 sec
Reputation Power: 1640

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

whoa, eh

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Viewing: Dev Shed Forums > Databases > MySQL Help > Slow select query with join

« Previous Thread | Next Thread »

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:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!