优化mysql查询 [英] Optimize mysql query

问题描述

我想优化这个查询，因为它需要很长时间来执行几乎一秒钟

这是查询:

IF 存在(从客户中选择 CustFirstName其中(客户名字 = 输入客户名字)或 (CustLastName= InputCustLastName)或(电子邮件 = 输入电子邮件));

所有这三列都有唯一索引.我有 765704 条记录.

这是我查询的解释结果集:

<前>----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+|身份证 |选择类型 |表|类型 |可能的密钥|键 |密钥长度 |参考 |行 |额外 |+----+-------------+-------+------+---------------------+------+---------+------+--------+-----------------------------------+|1 |简单 |客户 |所有 |客户名称 |空 |空 |空 |765704 |在推送条件下使用 where |+----+-------------+-------+------+---------------------+------+---------+------+--------+-----------------------------------+

谁能帮我优化一下.

解决方案

您似乎没有足够的索引.只有一个 possible_keys，您可能需要三个.将所有三个作为单个元组的唯一索引是不够的.

假设您已将所有三列都编入索引.Where (CustFirstName = InputCustFirstName) OR (CustLastName= InputCustLastName) OR (Email = InputEmail)) 通常会使查询优化器受挫.

使用 OR 将谓词更改为使用 UNION 的谓词:

稍微解释一下你的查询，你会改变

SELECT * FROM 客户WHERE CustFirstName = InputCustFirstName或 CustLastName = InputCustLastName或电子邮件 = 输入电子邮件

到

SELECT * FROM 客户WHERE CustFirstName = InputCustFirstName联盟SELECT * FROM 客户WHERE CustLastName = InputCustLastName联盟SELECT * FROM 客户WHERE 电子邮件 = 输入电子邮件

I want to optimize this query as it is taking long to execute almost a second

Here's the query:

IF Exists(
 Select CustFirstName From Customers
  Where (CustFirstName = InputCustFirstName)
     OR (CustLastName= InputCustLastName)
     OR (Email = InputEmail)
);

All these three columns have Unique index on it. and I have 765704 records in it.

This is the explain result set of my query :

----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+
| id | select_type | table | type | possible_keys        | key  | key_len | ref  | rows   | Extra                             |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+
|  1 | SIMPLE      | Customers | ALL  | CustName | NULL | NULL    | NULL | 765704 | Using where with pushed condition |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+

Can anyone assist me on how to optimize it.

解决方案

You don't have enough indexes, it would seem. There's only one possible_keys, where you probably need three. Having a unique index on all three of them as a single tuple isn't enough.

Suppose you have all three columns indexed. Where (CustFirstName = InputCustFirstName) OR (CustLastName= InputCustLastName) OR (Email = InputEmail)) will usually frustrate the query optimizer.

Change the predicate using OR to one using UNION:

To paraphrase your query somewhat, you would change

SELECT * FROM Customers
WHERE CustFirstName = InputCustFirstName
OR CustLastName = InputCustLastName
OR Email = InputEmail

to

SELECT * FROM Customers
WHERE CustFirstName = InputCustFirstName
UNION
SELECT * FROM Customers
WHERE CustLastName = InputCustLastName
UNION
SELECT * FROM Customers
WHERE Email = InputEmail

这篇关于优化mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！