468,531 Members | 1,622 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,531 developers. It's quick & easy.

Trouble with a very slow script

Hi,

I am responsible for a growing website. It is password protected and
we collect data on everyone who logs in. Every month we produce a
report on the trends of users which can result in some very unwieldy
sql statements. We are running MYSQL with SunOne ASP on a Solaris box.

Can anyone help with a way of improving the script - this currently
takes about a minute to complete!

Here is an example of a sql statement:

SELECT Count(stat_logins2.Email) AS TOTAL, Users_Details.Country FROM
stat_logins2, Users_Details WHERE (WhenLog = '20041015' OR WhenLog <
'20041015') AND (WhenLog = '20040415' OR WhenLog > '20040415') AND
stat_logins2.Email LIKE '%company1%' AND stat_logins2.Email NOT LIKE
'%company2%' AND stat_logins2.Email = Users_Details.Email GROUP BY
Users_Details.Country ORDER BY TOTAL DESC

This is supposed to return a list of total logins by country in
descending order. Below are the structures of the two tables:

stat_logins2 (holds the login data)
CREATE TABLE `stat_logins2` (
`ID` int(10) NOT NULL auto_increment,
`Email` varchar(50) NOT NULL default '',
`Company` varchar(100) NOT NULL default '',
`WhenLog` timestamp(14) NOT NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

Users_Details
CREATE TABLE `Users_Details` (
`ID` int(11) NOT NULL auto_increment,
`First_name` varchar(50) NOT NULL default '',
`Last_name` varchar(50) NOT NULL default '',
`Company` varchar(50) NOT NULL default '',
`Email` varchar(50) NOT NULL default '',
`Password` varchar(50) NOT NULL default '',
`Job_title` varchar(50) default NULL,
`Address1` varchar(50) default NULL,
`Address2` varchar(50) default NULL,
`Address3` varchar(50) default NULL,
`Postal_code` varchar(50) default NULL,
`Country` varchar(50) default NULL,
`Line_manager` varchar(50) default NULL,
`Phone` varchar(50) default NULL,
`Commreq` char(3) default NULL,
`Contact` varchar(50) NOT NULL default '',
`Temp_id` varchar(50) NOT NULL default '',
`Dateregistered` date default '0000-00-00',
`Date_reg` timestamp(14) NOT NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

Any help would be most appreciated!
Jul 20 '05 #1
1 1377
Chris Todhunter wrote:
Hi,

I am responsible for a growing website. It is password protected and
we collect data on everyone who logs in. Every month we produce a
report on the trends of users which can result in some very unwieldy
sql statements. We are running MYSQL with SunOne ASP on a Solaris box.

Can anyone help with a way of improving the script - this currently
takes about a minute to complete!

Here is an example of a sql statement:

SELECT Count(stat_logins2.Email) AS TOTAL, Users_Details.Country FROM
Use COUNT(*) instead of counting the Email field. Since your Email
field is NOT NULL, it's guaranteed to return the same value.
stat_logins2, Users_Details WHERE (WhenLog = '20041015' OR WhenLog <
'20041015') AND (WhenLog = '20040415' OR WhenLog > '20040415')
Is WhenLog indexed?

Also, you're going to run into trouble comparing WhenLog to date values,
if WhenLog contains any non-zero time value. In other words, what if
WhenLog has a value '20040415 10:25:32'?
AND
stat_logins2.Email LIKE '%company1%' AND stat_logins2.Email NOT LIKE
'%company2%'
LIKE predicates cannot use indexes in this case. If you need to speed
up these comparisons, store the domain portion of the email in separate
column, and compare with a string =. But try indexing your other fields
used in joins and comparisons first; that might solve the speed problem.
AND stat_logins2.Email = Users_Details.Email
I don't know if you have indexed the Email field in your two tables.
This could cause performance penalties to use non-indexed fields in your
join criteria.
GROUP BY Users_Details.Country ORDER BY TOTAL DESC


Try indexing Country. Or not -- it's likely that the distribution of
values in that field is 90% a single value, right? In which case an
index doesn't gain you much.

You're ordering by an aggregate field, so there's nothing to index there.

Bill K.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by yawnmoth | last post: by
4 posts views Thread by Adrian MacNair | last post: by
22 posts views Thread by Albert Oppenheimer | last post: by
4 posts views Thread by Claudio Calboni | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.