mysql date 索引性能,mysql – 使用索引提高查询性能

我希望为数据库创建一个索引,以获得更好的性能.

我正在使用此查询：

SELECT DISTINCT t1.* FROM current_order

AS t1 LEFT JOIN

receipt AS t2 USING (paper_id,subscriber_id)

WHERE t2.id NOT IN (SELECT id

FROM receipt WHERE paid_till_date > Now())

UNION

SELECT current_order.* FROM receipt RIGHT JOIN

current_order USING (paper_id, subscriber_id)

Where receipt.id IS NULL ORDER BY

subscriber_id, paper_id

表是：

PAPER

id

name

SUBSCRIBER

id

name

address

suburb

state

postcode

round_id

CURRENT ORDER

paper_id

subscriber_id

ROUND

id

name

paperboy

RECEIPT

id

receipt_date

paid_till_date

paper_id

subscriber_id

我知道主键已经被索引,并且我应该使用索引,其中经常使用WHERE子句和ORDER BY子句.所以我假设订阅者名称是一个很好的索引？

还有收据日期？

任何提示赞赏

解决方法:

原始查询

SELECT DISTINCT t1.*

FROM current_order AS t1

LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)

WHERE t2.id NOT IN (

SELECT id

FROM receipt

WHERE paid_till_date > Now()

)

UNION

SELECT current_order.*

FROM receipt

RIGHT JOIN current_order USING (paper_id, subscriber_id)

WHERE receipt.id IS NULL

ORDER BY subscriber_id, paper_id

比必要的更复杂.

基表是

CURRENT ORDER

paper_id

subscriber_id

和

RECEIPT

id

receipt_date

paid_till_date

paper_id

subscriber_id

OP声明receipt.id是主键,我认为(paper_id,subscriber_id)是当前订单的主键.

两个查询

SELECT t1.*

FROM current_order AS t1

LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)

WHERE t2.id NOT IN (

SELECT id

FROM receipt

WHERE paid_till_date > Now()

)

和

SELECT t1.*

FROM current_order AS t1

LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)

WHERE paid_till_date <= Now()

or paid_till_date is NULL

等价：结果集f两个查询都包含以下元组：

all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date <= Now()

all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date is NULL

all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore paid_till_date is NULL)

因此查询是等效的.如果将DISTINCT添加到select子句中,它们也是等效的.

查询的结果集

SELECT current_order.*

FROM receipt

RIGHT JOIN current_order USING (paper_id, subscriber_id)

WHERE receipt.id IS NULL

ORDER BY subscriber_id, paper_id

包含以下元组

all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore receipt.id is NULL)

因此原始查询可以更改为更简单的查询

SELECT DISTINCT t1.*

FROM current_order AS t1

LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)

WHERE paid_till_date <= Now()

or paid_till_date is NULL

也许(paper_id,subscriber_id,paid_till_date)上的索引将是有用的.该查询仅使用索引中找到的列(不再使用receipt.id). “当前订单”的(paper_id,subscriber_id)索引已经存在,因为这是主键.

标签：mysql,index

来源： https://codeday.me/bug/20190806/1595093.html