mysql中的嵌套选择加载时间
我有一桌数据包
数据包包含2个字段id和total
它们都是 bigint 长度 20 和主键
只有id是自增的
每条记录总计 = id
我有 10000 条记录，假设 id = 1 - 10000 且总计 = 1 - 10000
我有 2 个几乎相同的查询，其中 1 个导致加载时间非常长，一个几乎立即完成。
非常非常长的加载时间：
set @i = 0;
SELECT *
FROM packets as p1
WHERE p1.total in ( SELECT p2.total
FROM packets as p2 where ( ( @i := ( @i + 1 ) )
非常短的加载时间：
set @i = 0;
SELECT *
FROM packets as p1
WHERE p1.id in ( SELECT p2.id
FROM packets as p2 where ( ( @i := ( @i + 1 ) )
当 id 和total 的值相同时会发生这种情况。
I have one table packets
packets contains 2 fields id and total
they are both bigint length 20 and primary keys
only id is auto increment
in every record total = id
i got 10000 records let's say id = 1 - 10000 and total = 1 - 10000
i got 2 almost identical queries 1 results in a very very long loading time and one completes almost instantly.
very very long loading time:
set @i = 0;
SELECT *
FROM packets as p1
WHERE p1.total in ( SELECT p2.total
FROM packets as p2 where ( ( @i := ( @i + 1 ) )
very short loading time:
set @i = 0;
SELECT *
FROM packets as p1
WHERE p1.id in ( SELECT p2.id
FROM packets as p2 where ( ( @i := ( @i + 1 ) )
this happens while the values of id and total are identical.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您不能在一个表中有两个主键。
您似乎没有
packets.total上的索引创建它并查看它是否有帮助：
You cannot have two primary keys in one table.
It seems that you don't have an index on
packets.totalCreate it and see if it helps: