Hi,
Yes.... I think the following will definitely help...
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.
Also, because of they way MySQL may run the query, try running this, the
query defines the join between the two tables first...
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, I've read everything I can about optimizing queries but I think I'm
>still missing something.
>
>Here's the scenario:
>
>Table 'url' stores URLs and ID numbers.
>Table 'pageview' stores ID numbers (related to ID numbers in the url
>table), a count of how many times they were requested, and the date.
>
>mysql> describe url;
>+-------+--------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+--------------+------+-----+---------+----------------+
>| url | varchar(128) | | MUL | | |
>| id | int(11) | | PRI | 0 | auto_increment |
>+-------+--------------+------+-----+---------+----------------+
>
>mysql> describe pageview;
>+-------+-----------------------+------+-----+------------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+-----------------------+------+-----+------------+-------+
>| id | mediumint(8) unsigned | | MUL | 0 | |
>| date | date | | MUL | 0000-00-00 | |
>| site | int(11) | | | 0 | |
>| count | int(11) | | | 0 | |
>+-------+-----------------------+------+-----+------------+-------+
>
>I believe I have a good set of keys:
>
>mysql> show keys from url;
>+-------+------------+----------+--------------+-------------+-----------+--
>-----------+----------+
>| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
>Cardinality | Sub_part |
>+-------+------------+----------+--------------+-------------+-----------+--
>-----------+----------+
>| url | 0 | PRIMARY | 1 | id | A |
> 71070 | NULL |
>| url | 0 | urlkey | 1 | url | A |
> 71070 | 70 |
>+-------+------------+----------+--------------+-------------+-----------+--
>-----------+----------+
>
>mysql> show keys from pageview;
>+----------+------------+----------+--------------+-------------+-----------
>+-------------+----------+
>| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
>| Cardinality | Sub_part |
>+----------+------------+----------+--------------+-------------+-----------
>+-------------+----------+
>| pageview | 1 | urlidx | 1 | id | A
>| 78489 | NULL |
>| pageview | 1 | dateidx | 1 | date | A
>| 25 | NULL |
>+----------+------------+----------+--------------+-------------+-----------
>+-------------+----------+
>
>mysql> select sum(count) from pageview,url where url.url like '/sports/%'
>and pageview.date='1999-06-16' and url.id=pageview.id;
>+------------+
>| sum(count) |
>+------------+
>| 14398 |
>+------------+
>1 row in set (16 min 53.27 sec)
>
>this is too slow. table URL has 71070 unique URLs in it. table pageview
>has 1255834 rows (can't be unique because a given URL should show up across
>many days).
>
>mysql> explain select sum(count) from pageview,url where url.url like
>'/sports/%' and pageview.date='1999-06-16' and url.id=pageview.id;
>+----------+-------+----------------+---------+---------+------+-------+----
>---+
>| table | type | possible_keys | key | key_len | ref | rows |
>Extra |
>+----------+-------+----------------+---------+---------+------+-------+----
>---+
>| pageview | ref | urlidx,dateidx | dateidx | 3 | ??? | 38080 |
> |
>| url | range | PRIMARY,urlkey | urlkey | NULL | NULL | 3681 |
> |
>+----------+-------+----------------+---------+---------+------+-------+----
>---+
>
>this looks to me like it's picking the right keys. am i wrong? what else
>can i do to speed things up?
>
>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-thread6071@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.
>
>
Regards,
Kevin Smith
Lemon Lainey Design UK
Website: http://www.lemonlaineydesign.com
eMail: kevin@stripped
