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
---------------------------------------------------------------------
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
