Jon Drukman wrote:
>
> Unfortunately we have URLs which are unique out to about 100 chars so this
> won't work.
>
> I should probably find a way to break them up further... more data design
> ideas appreciated. :)
If the URLs are in well-defined sections, you could split them up on the '/'
and convert each section to an integer. Then your select:
>
> >PS: And if you have time, I would be interested in the EXPLAIN output
> >for the SELECT below.
>
> >> >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')));
would turn into something like
SELECT SUM(count) AS 'Total Views'
FROM pageview,url,url_parts
WHERE url.id = pageview.id
AND url.level1 = url_parts.id
AND url_parts.path = 'sports'
AND pageview.date = '1999-06-16';
or even, (say the id in url_parts for 'sports' is 12)
SELECT SUM(count) AS 'Total Views'
FROM pageview,url
WHERE url.id = pageview.id
AND url.level1 = 12
> Yesterday I decided that it was time to throw some money at the problem. I
> moved the database installation from a Pentium 200 with 80M RAM running
> FreeBSD 3.1 to a Sun Ultra 10 with 256M of RAM running Solaris. It is a
> *lot* faster now. That particular query's time went from 1 minute to 1.5
> seconds. Sometimes brute force is the best answer. :)
I'll bet that win was the increased memory. Do you have 256M around to put into the
FreeBSD box? :)
-dave
