MySQL慢连接

Question

我有一个MySQL查询，当我连接到第二个表时，该查询需要很长时间才能运行。

我的两个表格如下：

Table        Rows
properties   51,000
details      3,700,000

我的SQL语句是：

SELECT SQL_CALC_FOUND_ROWS a.id, a.address1, a.address2, a.address3, a.address4, a.address5, a.original_price, a.price, a.create_date, a.description, a.status, a.street, a.postcode, a.type_flag, AVG(coalesce(b.price,0)) as avg_price, coalesce((a.price - AVG( b.price )),0) AS difference, a.url, TIMESTAMPDIFF(MONTH, DATE(a.create_date), now()) as months, a.distance, a.expired, a.favourite
FROM properties a
LEFT JOIN details b ON a.street = b.street AND a.postcode = b.sector AND a.type_flag = b.type
WHERE a.price >= '0' AND a.price <= '200000' AND a.expired = 'N' AND a.status != 'R' AND a.flag = 'R'
GROUP BY a.id
ORDER BY  a.address1 asc
LIMIT 0, 50

我在表属性上有以下索引：

Index          Keys
status         status, flag
expired        expired
full_key       street, postcode, type_flag, expired, status, flag

表的详细信息和以下索引：

Index          Keys
address        type, street, sector

当我使用explain运行SQL语句时，我得到：

id  select_type  table  type  possible_keys   key      key_len  ref                       rows   Extra  
1   SIMPLE       a      ref   status,expired  expired  5        const                     14766  Using where; Using temporary; Using filesort
1   SIMPLE       b      ref   address         address  417      property.a.type_flag,     4
                                                                property.a.street,
                                                                property.a.postcode

你认为我应该强制这个查询使用索引'full_key‘吗？或者你认为我需要完全创建一个新的索引？

当我在没有连接的情况下运行它时，它运行得很快，所以看起来是对大型详细数据表的连接减慢了它的速度。

我测试的问题是，一旦我运行了一次查询，下一次它运行得非常快，所以我必须等待一天才能再次测试。

更新

这些是表方案

属性表：

Column           Type   
id               int(15)
agent            varchar(64)
street           varchar(128)
town             varchar(64)
county           varchar(64)
postcode         varchar(16)
type_flag        varchar(1) 
price            decimal(12,2)
status           varchar(1)
address1         varchar(128)
address2         varchar(128)
address3         varchar(128)
address4         varchar(128)
address5         varchar(128)
description      varchar(512)
type             varchar(32)
bedrooms         varchar(16)
flag             varchar(1)
expired          varchar(1)
favourite        varchar(1)     

明细表：

Column           Type
id               varchar(128)
price            decimal(12,2)
date             datetime
postcode         varchar(16)
type             varchar(1)
old_new          varchar(1)
duration         varchar(1)
paon             varchar(32)
saon             varchar(32)
street           varchar(128)
locality         varchar(128)
town_city        varchar(128)
local_authority  varchar(128)
county           varchar(128)
status           varchar(1)
sector           varchar(8)

更新2：

在将“expired”索引更改为expired、flag、status、price之后，查询的运行速度仍然非常慢。

下面是我现在解释时得到的结果：

id   select_type  table  type    possible_keys      key       key_len   ref                   rows    extra
1    SIMPLE       a      range   status,expired     expired   16        NULL                  1031    Using where; Using temporary; Using filesort
1    SIMPLE       b      ref     address            address   417       property.a.type_flag, 4
                                                                        property.a.street,
                                                                        property.a.postcode

因此，来自表属性的行数从14,766减少到1,031，但是查询花费了127.1271秒才能运行。

有没有可能让这个查询快速运行，或者在连接大型表时，MySQL是不是无用的？

Answer 1

这是您的查询：

SELECT SQL_CALC_FOUND_ROWS  . . .
FROM properties p LEFT JOIN
     details d
     ON p.street = d.street AND p.postcode = d.sector AND p.type_flag = d.type
WHERE p.price >= '0' AND p.price <= '200000' AND
      p.expired = 'N' AND p.status <> 'R' AND p.flag = 'R'
GROUP BY p.id
ORDER BY p.address1 asc
LIMIT 0, 50;

最好的索引是这样的：properties(expired, flag, price, status)或properties(expired, flag, status, price)和details(street, sector, type)。您可以使用后一种索引。

但是，对于where子句来说，properties的索引并不是特别有用。它使用的索引仅用于status条件。在where子句中，可以使用索引的许多其他条件。