Here is the first table 'tbl1':
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| val | varchar(45) | YES | MUL | NULL | |
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
+---------+---------------------+------+-----+---------+----------------+
With its indices:
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl1 | 0 | PRIMARY | 1 | id | A | 201826018 | NULL | NULL | | BTREE | |
| tbl1 | 1 | val | 1 | val | A | 2147085 | NULL | NULL | YES | BTREE | |
| tbl1 | 1 | id_val | 1 | id | A | 201826018 | NULL | NULL | | BTREE | |
| tbl1 | 1 | id_val | 2 | val | A | 201826018 | NULL | NULL | YES | BTREE | |
| tbl1 | 1 | val_id | 1 | val | A | 2147085 | NULL | NULL | YES | BTREE | |
| tbl1 | 1 | val_id | 2 | id | A | 201826018 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
(The reason for some extra indexing is this: http://bit.ly/KWx1Xz .)
The second table is about the same. Here are his indicators, though:
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl2 | 0 | PRIMARY | 1 | id | A | 201826018 | NULL | NULL | | BTREE | |
| tbl2 | 1 | val | 1 | val | A | 881336 | NULL | NULL | YES | BTREE | |
| tbl2 | 1 | id_val | 1 | id | A | 201826018 | NULL | NULL | | BTREE | |
| tbl2 | 1 | id_val | 2 | val | A | 201826018 | NULL | NULL | YES | BTREE | |
| tbl2 | 1 | val_id | 1 | val | A | 881336 | NULL | NULL | YES | BTREE | |
| tbl2 | 1 | val_id | 2 | id | A | 201826018 | NULL | NULL | | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
The task is to combine them in the val column and get a list of identifiers (and do it in 1 second).
Here is the "join" approach:
SELECT tbl1.id FROM tbl1 JOIN tbl2 ON tbl1.val = 'iii' AND tbl2.val = 'iii' AND tbl1.id = tbl2.id;
Result: 10,831 lines in a set ( 55.15 sec )
Request Explanation:
+----+-------------+--------+--------+----------------------------------+---------+---------+---------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+----------------------------------+---------+---------+---------------------------+------+--------------------------+
| 1 | SIMPLE | tbl1 | ref | PRIMARY,val,id_val,val_id | val_id | 138 | const | 5160 | Using where; Using index |
| 1 | SIMPLE | tbl2 | eq_ref | PRIMARY,val,id_val,val_id | PRIMARY | 8 | search_test.tbl1.id | 1 | Using where |
+----+-------------+--------+--------+----------------------------------+---------+---------+---------------------------+------+--------------------------+
And here is the 'in' approach:
SELECT id FROM tbl1 WHERE val = 'iii' and id IN (SELECT id FROM tbl2 WHERE val = 'iii');
Result: 10,831 lines per set ( 1 min 10.15 sec )
I explain:
+----+--------------------+--------+-----------------+---------------------------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------+-----------------+---------------------------------+---------+---------+-------+------+--------------------------+
| 1 | PRIMARY | tbl1 | ref | val,val_id | val_id | 138 | const | 8553 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl2 | unique_subquery | PRIMARY,val,id_val,val_id | PRIMARY | 8 | func | 1 | Using where |
+----+--------------------+--------+-----------------+---------------------------------+---------+---------+-------+------+--------------------------+
So here is the question: how to configure this query so that MySQL will execute it in a second?