List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 24 2001 1:32pm
Subject:Re: Query Copying to Temp Table
View as plain text  
Scott wrote:
> 
> I am running into a problem here with a general query that is essential
> to the vitality of a website that I run.  The problem is that every time
> this query is executed, it is being copied to a temporary table on the
> hard disk, causing an extreme slowdown.  In some instances it takes up
> to 5 minutes for the query to execute.  Here is the basic structure of
> the databases involved:
> 
> skins:
> +-------------+--------------+------+-----+---------------------+-------
> ---------+
> | Field       | Type         | Null | Key | Default             | Extra
> |
> +-------------+--------------+------+-----+---------------------+-------
> ---------+
> | id          | int(11)      |      | PRI | NULL                |
> auto_increment |
> | catid       | int(11)      |      | MUL | 0                   |
> |
> | userid      | int(11)      |      | MUL | 0                   |
> |
> | name        | varchar(30)  |      |     |                     |
> |
> | filesize    | varchar(5)   |      |     |                     |
> |
> | added       | datetime     |      | MUL | 0000-00-00 00:00:00 |
> |
> | widths      | varchar(4)   |      |     |                     |
> |
> | heights     | varchar(4)   |      |     |                     |
> |
> | widthl      | varchar(4)   |      |     |                     |
> |
> | heightl     | varchar(4)   |      |     |                     |
> |
> | comments    | int(11)      |      |     | 0                   |
> |
> | imagesmall  | varchar(50)  |      |     |                     |
> |
> | imagelarge  | varchar(50)  |      |     |                     |
> |
> | filename    | varchar(100) |      |     |                     |
> |
> | voteorig    | int(11)      |      |     | 0                   |
> |
> | votetech    | int(11)      |      |     | 0                   |
> |
> | voteoverall | int(11)      |      |     | 0                   |
> |
> | votecount   | int(11)      |      | MUL | 0                   |
> |
> | lastviewed  | int(11)      |      |     | 0                   |
> |
> | host        | varchar(30)  |      |     |                     |
> |
> +-------------+--------------+------+-----+---------------------+-------
> ---------+
> indexes on id, added, userid, votecount, catid
> 
> skindex:
> +----------+----------+------+-----+---------------------+--------------
> --+
> | Field    | Type     | Null | Key | Default             | Extra
> |
> +----------+----------+------+-----+---------------------+--------------
> --+
> | id       | int(11)  |      | PRI | NULL                |
> auto_increment |
> | category | int(11)  |      | MUL | 0                   |
> |
> | name     | char(30) |      | UNI |                     |
> |
> | title    | char(30) |      |     |                     |
> |
> | total    | int(11)  |      |     | 0                   |
> |
> | added    | datetime |      |     | 0000-00-00 00:00:00 |
> |
> | softid   | int(11)  |      |     | 0                   |
> |
> +----------+----------+------+-----+---------------------+--------------
> --+
> indexes on id, name, category
> 
> index:
> +-------+----------+------+-----+---------------------+----------------+
> | Field | Type     | Null | Key | Default             | Extra          |
> +-------+----------+------+-----+---------------------+----------------+
> | id    | int(11)  |      | PRI | NULL                | auto_increment |
> | name  | char(40) |      | UNI |                     |                |
> | total | int(11)  |      |     | 0                   |                |
> | added | datetime |      |     | 0000-00-00 00:00:00 |                |
> | title | char(20) |      |     |                     |                |
> +-------+----------+------+-----+---------------------+----------------+
> indexes on id, name
> 
> skin_descs:
> +-------------+---------+------+-----+---------+-------+
> | Field       | Type    | Null | Key | Default | Extra |
> +-------------+---------+------+-----+---------+-------+
> | id          | int(11) |      | PRI | 0       |       |
> | description | text    |      | MUL |         |       |
> +-------------+---------+------+-----+---------+-------+
> indexes on id
> 
> skin_stats:
> +------------+---------+------+-----+---------+-------+
> | Field      | Type    | Null | Key | Default | Extra |
> +------------+---------+------+-----+---------+-------+
> | id         | int(11) |      | PRI | 0       |       |
> | views      | int(11) |      | MUL | 0       |       |
> | viewstoday | int(11) |      |     | 0       |       |
> | dltotal    | int(11) |      | MUL | 0       |       |
> | dltoday    | int(11) |      |     | 0       |       |
> | dluserid1  | int(11) |      |     | 0       |       |
> | dluserid2  | int(11) |      |     | 0       |       |
> | dluserid3  | int(11) |      |     | 0       |       |
> | dluserid4  | int(11) |      |     | 0       |       |
> | dluserid5  | int(11) |      |     | 0       |       |
> +------------+---------+------+-----+---------+-------+
> indexes on id, views, dltotal
> 
> users:
> +-----------------+-----------+------+-----+---------------------+------
> ----------+
> | Field           | Type      | Null | Key | Default             | Extra
> |
> +-----------------+-----------+------+-----+---------------------+------
> ----------+
> | ID              | int(11)   |      | PRI | NULL                |
> auto_increment |
> | username        | char(20)  |      | UNI |                     |
> |
> | access          | char(12)  |      | MUL |                     |
> |
> +-----------------+-----------+------+-----+---------------------+------
> ----------+
> indexes on id
> 
> Now here is the base query that is always being choked on, causing mysql
> to freak out:
> 
> SELECT users.username, users.access, skins.name AS skinname, skins.id AS
> skinid, skins.voteorig, skins.votetech, skins.voteoverall,
> (skins.votecount * (skins.voteorig + skins.votetech +
> skins.voteoverall)) + ((skin_stats.dltoday + skin_stats.viewstoday) *
> 100) AS rating, skindex.name AS secname, skindex.title AS sectitle,
> index.name AS catname, index.title AS cattitle FROM users, index,
> skindex, skins, skin_stats WHERE skindex.category=index.id AND
> skins.catid=skindex.id AND skin_stats.id=skins.id AND
> skins.userid=users.ID ORDER BY rating DESC LIMIT 0,5
> 
> There are other slight variations to that, that just narrow the query
> down to a particular index.name or skindex.name.  Either way, they
> generally all take the same time to process.
> 
> If I describe the query, I get the following:
> 
> +------------+--------+----------------------+---------+---------+------
> ------------+-------+---------------------------------+
> | table      | type   | possible_keys        | key     | key_len | ref
> | rows  | Extra                           |
> +------------+--------+----------------------+---------+---------+------
> ------------+-------+---------------------------------+
> | skin_stats | ALL    | PRIMARY              | NULL    |    NULL | NULL
> | 25888 | Using temporary; Using filesort |
> | skins      | eq_ref | PRIMARY,userid,catid | PRIMARY |       4 |
> skin_stats.id    |     1 |                                 |
> | skindex    | eq_ref | PRIMARY,category     | PRIMARY |       4 |
> skins.catid      |     1 |                                 |
> | index      | eq_ref | PRIMARY              | PRIMARY |       4 |
> skindex.category |     1 |                                 |
> | users      | eq_ref | PRIMARY              | PRIMARY |       4 |
> skins.userid     |     1 |                                 |
> +------------+--------+----------------------+---------+---------+------
> ------------+-------+---------------------------------+
> 
> Can anyone offer any insight into why MySQL isnt optimizing the query in
> memory but rather using the temporary caching?  This is a real
> hinderence that is putting extreme pressure on my site, which I need to
> rectify soonest.  I appreciate any help.
> 
> Regards,
> 
> Scott
> 
That ORDER by a calculated_field DESC is killing you.
It needs to go to a temporary table for sorting.
Thread
Query Copying to Temp TableScott24 Apr
  • Re: Query Copying to Temp TableGerald Clark24 Apr
    • RE: Query Copying to Temp TableScott25 Apr
      • Re: Query Copying to Temp TableSeung-woo Nam25 Apr
        • Re: Query Copying to Temp TableThalis A. Kalfigopoulos25 Apr