优化mysql查询
我想优化这个查询,因为它需要很长时间才能执行几乎一秒钟
这是查询:
IF Exists(
Select CustFirstName From Customers
Where (CustFirstName = InputCustFirstName)
OR (CustLastName= InputCustLastName)
OR (Email = InputEmail)
);
所有这三列都有唯一索引。我里面有 765704 条记录。
这是我的查询的解释结果集:
----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+ | 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 | +----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------------+
任何人都可以帮助我如何优化它。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
看来您没有足够的索引。只有一个
possible_keys
,而您可能需要三个。将所有三个作为单个元组拥有唯一索引是不够的。假设您已对所有三列建立了索引。
Where (CustFirstName = InputCustFirstName) OR (CustLastName= InputCustLastName) OR (Email = InputEmail))
通常会让查询优化器感到沮丧。将使用
OR
的谓词更改为使用UNION
的谓词:为了稍微解释一下您的查询,您可以更改
为
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 usingUNION
:To paraphrase your query somewhat, you would change
to
一秒查询 3/4 百万条记录索引三次并返回所有三个查询的并集?听起来很合理,除非您有一台配备 15K RPM SAS 或 SCSI 磁盘的非常快的服务器。
您可以尝试将其重新编码为三个单独查询的联合,每个查询对应一个列条件。这可能允许它为每列使用索引。
One second to query a 3/4 million record index three times and return the union of all three queries? Sounds reasonable unless you have a really fast server with 15K RPM SAS or SCSI disks.
You might try recoding it as a union of three separate queries, one for each column criterion. That might allow it to use an index for each column.