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
