Trouble with a very slow script

[Chris Todhunter]

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_logi ns2.Email) AS TOTAL, Users_Details.C ountry FROM
stat_logins2, Users_Details WHERE (WhenLog = '20041015' OR WhenLog <
'20041015') AND (WhenLog = '20040415' OR WhenLog > '20040415') AND
stat_logins2.Em ail LIKE '%company1%' AND stat_logins2.Em ail NOT LIKE
'%company2%' AND stat_logins2.Em ail = Users_Details.E mail GROUP BY
Users_Details.C ountry 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!

[Bill Karwin]

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_logi ns2.Email) AS TOTAL, Users_Details.C ountry 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.Em ail LIKE '%company1%' AND stat_logins2.Em ail 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.Em ail = Users_Details.E mail
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.C ountry 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.