MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Kevin Smith Date:June 28 1999 10:37pm
Subject:Re: Yet Another Optimization Question
View as plain text  
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

Thread
adding data is slowJon Drukman24 Jun
  • Re: adding data is slowSasha Pachev24 Jun
    • Re: adding data is slowJon Drukman25 Jun
      • Re: adding data is slowBenjamin Pflugmann25 Jun
        • Re: adding data is slowJon Drukman25 Jun
        • Yet Another Optimization QuestionJon Drukman28 Jun
  • Re: adding data is slowDaniel Koch25 Jun
  • adding data is slowMichael Widenius29 Jun
Re: Yet Another Optimization QuestionKevin Smith29 Jun
Re: Yet Another Optimization QuestionJon Drukman29 Jun
  • Re: Yet Another Optimization QuestionBenjamin Pflugmann30 Jun
    • Re: Yet Another Optimization QuestionJon Drukman30 Jun
Re: Yet Another Optimization QuestionKevin Smith29 Jun
Re: Yet Another Optimization QuestionJon Drukman29 Jun
  • Re: Yet Another Optimization QuestionT├Ánu Samuel29 Jun
    • Re: Yet Another Optimization QuestionJon Drukman29 Jun
      • Re: Yet Another Optimization QuestionMichael Widenius30 Jun
        • Re: Yet Another Optimization QuestionJon Drukman30 Jun
RE: Yet Another Optimization QuestionNigel Parker30 Jun
  • RE: Yet Another Optimization QuestionMichael Widenius30 Jun
Re: Yet Another Optimization Question(David Sklar)1 Jul
RE: Yet Another Optimization Questiontony2 Jul
  • RE: Yet Another Optimization QuestionMichael Widenius2 Jul
  • slow joinsJon Drukman7 Oct