MySQL慢连接

Question

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

我的2个表如下:

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 (街道、部门、类型)。你有后一个索引。

但是，properties 的索引对于 where 子句不是特别有用。它使用的索引仅适用于 status 条件。 where 子句中还有很多其他条件可以使用索引。