Slow MySQL Query, Group By Order By Limit

Question

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

I'm not sure that you have indices for all the joins, and this would mean that grouping would still be slow even if you had indices for that. — Tim Biegeleisen

e4c5 e4c5 · Accepted Answer · 2016-11-15T11:39:57

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

Slow MySQL Query, Group By Order By Limit

2 Answers