我目前连接5个表,选择20个对象显示给用户,不幸的是,如果我使用GROUP BY
和ORDER BY
,它会变得非常慢。
查询示例如下:
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
查询的EXPLAIN显示如下:
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
| 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 | |
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+
所以它看起来没有使用表objekte
的键,分析说它使用2.7s来复制到tmp表。
代替FROM angebote
或JOIN objekte
我尝试了(SELECT * GROUP BY id)
,但不幸的是,这并没有改善。
WHERE
、ORDER BY
和GROUP BY
使用的字段也被索引。
我想我遗漏了一些基本概念,如果有任何帮助,我将不胜感激。
由于我很可能在表格上犯了错误,这里是对它们的描述:
Objekte
<前>+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+CREATE TABLE ' objeckte '' id ' int(11) NOT NULL,' type ' 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;'图片' varchar(255) NOT NULL,' last_offer ' int(11) NOT NULL,主键(' id '),键' minprice ' (' minprice '),关键字'评级'('评级'),键' last_offer ' (' last_offer ')) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+前>Angebote
<前>+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+创建angebote表' id ' int(11) NOT NULL;' 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,主键(' id '),唯一键' unique_key ' (' fid_object ', ' fid_land ', ' fid_region ', ' fid_subregion '),键' letztes_angebot ' (' letztes_angebot '),键' fid_objekt ' (' fid_objekt '),键' fid_land ' (' fid_land '),键' fid_region ' (' fid_region '),关键字' fid_subregion ' (' fid_subregion ')) ENGINE=InnoDB AUTO_INCREMENT=2433073 DEFAULT CHARSET=utf8 |+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+前>laender、regionen subregionen相同(结构)
<前>+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+创建laender表' id ' int(11) NOT NULL;' iso ' varchar(2) NOT NULL,' name ' varchar(255) NOT NULL,' slug ' varchar(255) NOT NULL,' letztes_angebot ' int(11) NOT NULL,主键(' id '),唯一密钥' iso ' (' iso '),键' slug ' (' slug '),关键字' letztes_angebot ' (' letztes_angebot ')) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8 |+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+前>首先这是一个非标准组由。因此,当你升级到mysql 5.7时,它将停止工作。
最大的问题是没有在objekte
表上使用索引。更糟糕的是,您在该表的评级字段上进行排序,但索引仍然没有被使用。一个可能的解决方案是创建一个复合索引,如下所示:
CREATE INDEX objekte_idx ON objekte(id,rating);
这里不需要使用GROUP BY。您没有使用聚合函数。因此,从查询中删除GROUP BY。删除Group By将提高查询性能。也不需要为limit定义0。
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