I have a table of emails with about 800 million entries, and I need to match it against a list of about 100,000 emails. The email entry in the database is indexed.
What would be the best query to do it? I've tried creating a temporary table with the entries I want to match against and then doing a SELECT against that and the main table, but it seems like it should be faster than it is - multiple queries seem to slow down. Any suggestions?
The server is running MySQL 5.5.27 on CentOS 6.3.
EDIT: Here are the creation statements:
CREATE TEMPORARY TABLE temptable (Email varchar(50))
CREATE INDEX tempindex ON temptable (Email)
And here's my query:
SELECT temptable.Email FROM temptable, biglist WHERE temptable.Email = biglist.Email
And here's the EXPLAIN result:
+----+-------------+-----------+-------+---------------+-----------+---------+-----------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+-----------------------+------+--------------------------+
| 1 | SIMPLE | temptable | index | tempindex | tempindex | 53 | NULL | 21 | Using index |
| 1 | SIMPLE | biglist | ref | idx_Email | idx_Email | 202 | datab.temptable.email | 1 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-----------+---------+-----------------------+------+--------------------------+
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `biglist` ADD INDEX `biglist_idx_email` (`Email`);
ALTER TABLE `temptable` ADD INDEX `temptable_idx_email` (`Email`);
SELECT
temptable.Email
FROM
temptable,
biglist
WHERE
temptable.Email = biglist.Email