MySQL 没有在带有“IN”的子查询的外部查询上使用键。

发布于 2024-08-24 14:47:43 字数 549 浏览 4 评论 0原文

我正在查询这样的数据库：

 SELECT * from Log WHERE cookieId IN (select cookieId from Log WHERE someId="blafasel");

我在 someId 和 cookieId 上有键，但查询非常慢。如果我运行两个查询（外部和内部），它们就会分开或非常快。

 select cookieId from Log WHERE someId="blafasel"

几乎立即给我结果。使用 EXPLAIN 的查询也是如此，

 SELECT * FROM Log WHERE cookieId IN ("something","somethingelse","athirdoption")

告诉我键在两个单个查询中使用，但对于子选择查询键仅用于内部选择。我的问题是为什么？以及如何告诉 MySQL 更聪明一点。

好吧，我可以让我的应用程序分开运行两个查询，但这并不方便。

感谢您的帮助。

原文

i'm querying a database like this:

 SELECT * from Log WHERE cookieId IN (select cookieId from Log WHERE someId="blafasel");

I have keys on someId and cookieId but yet the query is very slow.
If I run the two queries (the outer and the inner) separated both of them or very fast.

 select cookieId from Log WHERE someId="blafasel"

gets me the results almost instant. And so does a query

 SELECT * FROM Log WHERE cookieId IN ("something","somethingelse","athirdoption")

Using EXPLAIN tells me that keys are used in the two single queries but for the subselect query keys are only used for the inner select. My Question is why? And how to tell MySQL to be a little bit more clever.

Well I could let my application run the two queries separated but that wouldnt be convenient.

Thanks for your help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

允世 2024-08-31 14:47:43

你有没有尝试过使用

SELECT  l.*
FROM    log l INNER JOIN
        log ls ON l.cookieid = ls.cookieid
WHERE   ls.someId="blafasel"

Have you tried using

SELECT  l.*
FROM    log l INNER JOIN
        log ls ON l.cookieid = ls.cookieid
WHERE   ls.someId="blafasel"

回复收藏 0 原文

顾冷 2024-08-31 14:47:43

你简单地尝试过这个吗？

SELECT * from Log WHERE someId="blafasel";

我想你真正的查询有点不同，但以防万一它不是。

另外，解释是否在“可能的键”列中显示正确的键？如果是这样，您可以尝试在外部选择上使用“强制索引（index_name）”子句，看看是否可以加快速度。

Have you tried simply this?

SELECT * from Log WHERE someId="blafasel";

I imagine your real query is a bit different, but just in case it's not.

Also, does explain show the correct key in the "possible keys" column? If so, you could try using a "force index(index_name)" clause on the outer select to see if that speeds it up.

回复收藏 0 原文