Is using IN (...) the most efficient way to randomly access a MySQL table?
I have a table with 2.4M + numbers and no indices. I am 100% sure that all rows have one column (we will call it id
) which is unique, it is of type VARCHAR(255)
.
Now I have a file about 10,000 in size id
and need to pull the entire line for each one.
Using IN(...)
my best option? Should I add an index?
I was thinking about thinking about something like this:
SELECT * FROM archive_table WHERE id IN('id1', 'id2', ... 'idn');
It is effectively archived data and is only available to me every few weeks.
System: MySQL 5.0.45 Table: MyISAM
a source to share
Yes, add an index for both tables (2.4mil and 10000).
Assuming the transaction_table is 10,000 rows, the archive_table is 2.4 mil rows, and you've created an overr archive_table index that you could encode:
SELECT id
FROM transaction_table a
WHERE EXISTS( SELECT *
FROM archive_table b
WHERE a.id = b.id )
Using the EXISTS clause over a JOIN is more readable and has the same performance as a join.
a source to share
Add an index to the identity column and (optionally) define it as UNIQUE. This will help MySQL find the rows you want quickly, because the index contains the identifier in sorted order. Even if your table was sorted like e. d. because you are inserting in ascending order of identifiers, MySQL does not know this and will always do a full table scan to find matching records for your queries.
With an index, on the other hand, searching becomes very easy for the server. Only if you ask really, really many rows at once (very long IN () clause), the optimizer might decide that you want to get more than 30% of the data - in which case it will fall back to linear scan again to prevent excessive disk lookups.
However, with a few million lines, it would be a heck of a long condition :)
I would also recommend reconsidering if the column really should be 255 characters long - even if the VARCHAR won't use up as much space when you don't need it, it sounds like a questionable design. Whether it is a numeric field or not may depend on your needs, however it is generally recommended.
a source to share
From what our DBA previously knew, the "IN" clause has a limit on the number of explicit identifiers that can be in parentheses. I was told that this does not apply if you can use SELECT to feed the IN list.
HLGEM's suggestion for importing and using a join into a table is probably the simplest approach. And, as mentioned earlier, indexing will improve performance.
a source to share