Is The Query Scalable? 1

[3dColor]

Rudy helped me create this query a couple of years ago to find the stats for each day of an rental ad's duration. It worked great before but now it is taking 17 seconds to process the query with over 66k records in the table.

Is the query scalable?

<!--- retrieve stats past 40 days for an ad --->
	<cffunction access="remote" name="retrieveStats40days" returntype="query">
		<cfargument name="adId"	type="numeric" required="true"/>
		<cfargument name="adduration" type="numeric" required="true"/>
		<CFQUERY name="GetStats40days" datasource="#DSN#">
			SELECT thedate, 
				count(Stats.timeStampStats) as cnt
  				FROM (
       				select date_sub(current_date, interval i day) as thedate
        		 from Integers t
        		where i <= #adduration# 
       		) as i_40 
			LEFT OUTER
     		 join Stats
      		  on date(Stats.timeStampStats)
      		   = i_40.thedate
 	 		 and Stats.listingFk = <cfqueryparam value='#adId#' cfsqltype='cf_sql_integer'>  
			GROUP BY thedate
			ORDER BY thedate DESC
		</CFQUERY>
	<cfreturn GetStats40days>
	</cffunction>

 

[r937]

what indexes have you defined on the tables?

r937.com | rudy.ca

 

[3dColor]

Good morning Rudy!

The Stats table just has one Primary index key which is "statsId".

The Integers table just has one column and Primary index key is "i".

Here is the source code for both tables:

CREATE TABLE `Stats` (
  `statsId` int(11) unsigned NOT NULL auto_increment,
  `listingFk` int(11) unsigned default NULL,
  `timeStampStats` timestamp NULL default CURRENT_TIMESTAMP,
  `ipAddress_stats` varchar(18) default NULL,
  PRIMARY KEY  (`statsId`)
) ENGINE=MyISAM AUTO_INCREMENT=982781 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `Integers` (
  `i` int(11) NOT NULL default '0',
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thanks Rudy,
Dave

 

[r937]

ALTER TABLE Stats ADD INDEX nummy (timeStampStats)

indexing is the key to performance

then change this --

LEFT OUTER
JOIN Stats
ON date(Stats.timeStampStats) = i_40.thedate

to this --

LEFT OUTER
JOIN Stats
ON Stats.timeStampStats >= i_40.thedate
AND Stats.timeStampStats < DATE_ADD(i_40.thedate, INTERVAL 1 DAY)


r937.com | rudy.ca

 

[3dColor]

Thanks Rudy!

I will give that a try and report back...

 

[3dColor]

Rudy,

I change the table so now it reads:

CREATE TABLE `stats` (
  `statsId` int(11) unsigned NOT NULL auto_increment,
  `listingFk` int(11) unsigned default NULL,
  `timeStampStats` timestamp NULL default CURRENT_TIMESTAMP,
  `ipAddress_stats` varchar(18) default NULL,
  PRIMARY KEY  (`statsId`),
  KEY `nummy` (`timeStampStats`)
) ENGINE=MyISAM AUTO_INCREMENT=839561 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

and now the Query is this:

<cffunction access="remote" name="retrieveStats40days" returntype="query">
		<cfargument name="adId"	type="numeric" required="true"/>
		<cfargument name="adduration" type="numeric" required="true"/>
		<CFQUERY name="GetStats40days" datasource="#DSN#">
			SELECT thedate, 
				count(Stats.timeStampStats) as cnt
  				FROM (
       				select date_sub(current_date, interval i day) as thedate
        		 from Integers t
        		where i <= #adduration# 
       		) as i_40 
			LEFT OUTER
				JOIN Stats
				ON Stats.timeStampStats >= i_40.thedate
				AND Stats.timeStampStats < DATE_ADD(i_40.thedate, INTERVAL 1 DAY)
 	 			and Stats.listingFk = <cfqueryparam value='#adId#' cfsqltype='cf_sql_integer'>  
			GROUP BY thedate
			ORDER BY thedate DESC
		</CFQUERY>
	<cfreturn GetStats40days>
	</cffunction>

But it still takes 17 seconds. Did I do the changes right?

 

[r937]

do me a favour, pull the query out of the coldfusion, substitute actual values for #adduration# and #adId#, then run an EXPLAIN on it directly in mysql



r937.com | rudy.ca

 

[3dColor]

Ok I did as you asked in MySQL Query Browser and I got the same 17 seconds result.

I am not sure I got all the lines right - this is what I used:

SELECT thedate,
count(Stats.timeStampStats) as cnt
FROM (
select date_sub(current_date, interval i day) as thedate
from Integers t
where i <= 40
) as i_40
LEFT OUTER
JOIN Stats
ON Stats.timeStampStats >= i_40.thedate
AND Stats.listingFk = '8342'
GROUP BY thedate
ORDER BY thedate DESC

 

[r937]

please stick the word EXPLAIN in front of SELECT and run it again

i got a feeling we should include listingFk into `nummy`

if you have time, try it with listingFk first, do an EXPLAIN, then try it again with it second, after timeStampStats, and do another EXPLAIN

r937.com | rudy.ca

 

[3dColor]

When I place EXPLAIN at the very top this is what returns:

1 primary <derived2>
1 primary stats
2 derived t

I am not sure how to alter the stats table, is this correct?

ALTER TABLE Stats ADD INDEX nummy (listingFk)

 

[r937]

yes, that's what EXPLAIN produces, but you've only shown part of it

ALTER TABLE Stats DROP INDEX nummy ;
ALTER TABLE Stats ADD INDEX nummy (listingFk,timeStampStats)

do you have the mysql documentation handy?

r937.com | rudy.ca

 

[3dColor]

It is running really fast now (less than 1 second) but something look very wrong to the graph now.

Take a look at this image of the output:

http://www.solardave.com/wp-content/uploads/2008/01/rudy.jpg

Appears to be a log curve heading up to 59 and then it levels out, which is not what is coming from the DB.

I do not have the mysql documentation handy.

 

[3dColor]

Rudy,

Now it is working! And it is working fast - around 250ms - much better than 17 seconds!

Thanks for your help.

I am not sure what I did but it is working now. I think it was when I added the nummy stuff that made it work.

 

[r937]

I think it was when I added the nummy stuff that made it work.

thank you, that's "quote of the week" material



my job here is done

;-)



r937.com | rudy.ca