[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Yet Another Optimization Question
From:       sklar () student ! net (David Sklar)
Date:       1999-06-30 20:33:47
[Download RAW message or body]

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




---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread6317@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@lists.mysql.com instead.

[prev in list] [next in list] [prev in thread] [next in thread]