Nested selection loading time in mysql

Question

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.

Answer 1

they are both bigint length 20 and primary keys

You cannot have two primary keys in one table.

It seems that you don't have an index on packets.total

Create it and see if it helps:

CREATE INDEX ix_packets_total ON packets (total)