Hi Jon,
Thanks for letting me know. :-)
Just a thought, in your select query you use the like for the column
url.url, which inevitably will slow it down a bit.
Can you not produce stats in one whole query, export the results to a
text file and then split the results later?
For example...
SELECT url.url AS URL, SUM(count) AS 'Total Views'
FROM pageview, url
WHERE (((url.id)=(pageview.id))
AND ((pageview.date)=('1999-06-16')))
GROUP BY url.url
ORDER BY url.url;
I'm not too sure if the GROUPING will have the same affect, but it should
be quicker.
>At 03:37 PM 6/28/99 , Kevin Smith wrote:
>>The id from the table url is set as INT(11) yet the id from pageview is
>>set as mediumint(8) unsigned. I believe, if these were set up the same
>>i.e. either both as INT(11) or both as mediumint(8). This should help
>>increase the speed.
>
>done.
>
>>Also, because of they way MySQL may run the query, try running this, the
>>query defines the join between the two tables first...
>
>funny, i would have thought that that would be slower. i thought ordering
>the where clauses so the most restrictive part came first would reduce the
>amount of work, but i guess i was mistaken.
>
>>SELECT SUM(count) as 'Total Views'
>>FROM pageview, url
>>WHERE (((url.id)=(pageview.id))
>>AND ((url.url like '/sports/%'))
>>AND ((pageview.date)=('1999-06-16')));
>>
>>Let me know how you get on, I would infact be VERY interested.
>
>OK, it's certainly a vast improvement:
>
>+-------------+
>| Total Views |
>+-------------+
>| 14398 |
>+-------------+
>1 row in set (1 min 14.45 sec)
>
>still a little pokey considering i have to run several dozen similar
>queries for each days stats but i guess i can deal.
>
>thanks for your help!
>
>
>Jon Drukman
>Director Of Technology
>GameSpot
>
Regards,
Kevin Smith
Lemon Lainey Design UK
Website: http://www.lemonlaineydesign.com
eMail: kevin@stripped
