At 04:59 PM 6/29/99 , Benjamin Pflugmann wrote:
>Additionally you should make sure the key buffer fits to your
>needs. 'urlkey' alone needs about 10MB and should fit into the key
>buffer for best perfomance, IMHO.
I'd been running with key_buffer=24M.
>Depending on your queries you might consider changing 'urlkey' to only
>consist of the leftmost part of your urls, e.g.
>
>UNIQUE urlkey (url(20))
>
>This would need only about 1MB and can be loaded faster therefore. But
>this change will slow down queries which search for exact URLs
>(i.e. everything what could benefit from a more precise query).
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. :)
>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')));
+----------+-------+----------------+--------+---------+--------+------+----
---+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+----------+-------+----------------+--------+---------+--------+------+----
---+
| url | range | PRIMARY,urlkey | urlkey | NULL | NULL | 3681 |
|
| pageview | ref | urlidx,dateidx | urlidx | 3 | url.id | 16 |
|
+----------+-------+----------------+--------+---------+--------+------+----
---+
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. :)
Jon Drukman
Director Of Technology
GameSpot
