Slow MySQL Query, Group By Order By Limit
I currently join 5 tables to select 20 objects to show the user, unfortunately if I use GROUP BY and ORDER BY it gets really slow.
An example query looks Like this:
SELECT r.name, l.name, o.typ, o.id, persons, children, description, rating, totalratings, minprice, picture FROM angebote as a
JOIN objekte as o ON a.fid_objekt = o.id
JOIN regionen as r ON a.fid_region = r.id
JOIN laender as l ON a.fid_land = l.id
WHERE l.slug="aegypten" AND a.letztes_angebot >= 1
GROUP BY a.fid_objekt ORDER BY rating DESC LIMIT 0,20
The EXPLAIN of the Query shows this:
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
| 1 | SIMPLE | l | ref | PRIMARY,slug | slug | 767 | const | 1 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | o | ALL | PRIMARY | NULL | NULL | NULL | 186779 | Using join buffer (flat, BNL join) |
| 1 | SIMPLE | a | ref | unique_key,letztes_angebot | unique_key | 8 | ferienhaeuser.o.id,ferienhaeuser.l.id | 1 | Using where |
| 1 | SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 4 | ferienhaeuser.a.fid_region | 1 | |
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
So it looks like it doesn't use a key for the table objekte , the Profiling says it uses 2.7s for Copying to tmp table.
Instead of FROM angebote or JOIN objekte I tried it with (SELECT * GROUP BY id) but unfortunately this doesn't improve.
The fields used for WHERE , ORDER BY and GROUP BY are also indexed.
I think I missed some basic concept here and any help will be appreciated.
Since it's most probable I made a mistake with the Tables, here the description of them:
Objekte
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | objekte | CREATE TABLE `objekte` ( `id` int(11) NOT NULL, `typ` varchar(50) NOT NULL, `persons` int(11) NOT NULL, `children` int(11) NOT NULL, `description` text NOT NULL, `rating` float NOT NULL, `totalratings` int(11) NOT NULL, `minprice` float NOT NULL, `picture` varchar(255) NOT NULL, `last_offer` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `minprice` (`minprice`), KEY `rating` (`rating`), KEY `last_offer` (`last_offer`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Angebote
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| angebote | CREATE TABLE `angebote` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fid_objekt` int(11) NOT NULL,
`fid_land` int(11) NOT NULL,
`fid_region` int(11) NOT NULL,
`fid_subregion` int(11) NOT NULL,
`letztes_angebot` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_key` (`fid_objekt`,`fid_land`,`fid_region`,`fid_subregion`),
KEY `letztes_angebot` (`letztes_angebot`),
KEY `fid_objekt` (`fid_objekt`),
KEY `fid_land` (`fid_land`),
KEY `fid_region` (`fid_region`),
KEY `fid_subregion` (`fid_subregion`)
) ENGINE=InnoDB AUTO_INCREMENT=2433073 DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
laender, regionen, subregionen (same structure)
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | laender | CREATE TABLE `laender` ( `id` int(11) NOT NULL AUTO_INCREMENT, `iso` varchar(2) NOT NULL, `name` varchar(255) NOT NULL, `slug` varchar(255) NOT NULL, `letztes_angebot` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `iso` (`iso`), KEY `slug` (`slug`), KEY `letztes_angebot` (`letztes_angebot`) ) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8 | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
First of all this is a non standard group by. As such it will stop working when you upgrade to mysql 5.7.
The biggest problem comes from the fact that no index is used on the objekte table. To make matters worse you are ordering on the ratings field on that table but the index is still not being used. A possible solution is to create a composite index like this:
CREATE INDEX objekte_idx ON objekte(id,rating);
You do not need to use GROUP BY here. You have not use aggregrate functions. So remove GROUP BY from query. Remove the Group By will increase query performance. Also no need to define 0 for limit.
SELECT r.name, l.name, o.typ, o.id, persons, children, description, rating, totalratings, minprice, picture FROM angebote as a
JOIN objekte as o ON a.fid_objekt = o.id
JOIN regionen as r ON a.fid_region = r.id
JOIN laender as l ON a.fid_land = l.id
WHERE l.slug="aegypten" AND a.letztes_angebot >= 1
ORDER BY rating DESC LIMIT 20
下一篇: 缓慢的MySQL查询，按限制排序