Hi.
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.
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).
Bye,
Benjamin.
PS: And if you have time, I would be interested in the EXPLAIN output
for the SELECT below.
On Mon, Jun 28, 1999 at 04:23:59PM -0700, jsd@stripped wrote:
> 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
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread6112@stripped
>
> 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@stripped instead.
Attachment: [application/pgp-signature]
