>That ORDER by a calculated_field DESC is killing you.
>It needs to go to a temporary table for sorting.
The problem, though, is that even if I leave the ORDER clause out, or
ORDER BY any other field, the same thing happens. I initially thought
the same thing, but it happens with or without the ORDER BY clause.
Scott
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
On Behalf Of Gerald Clark
Sent: Tuesday, April 24, 2001 10:33 PM
To: Scott
Cc: [EMAIL PROTECTED]
Subject: Re: Query Copying to Temp Table
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.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
