CODEWITHSUNDEEP
mysqlsqldatabase
Tigers
Tigers

Reputation: 23

Is there a better alternative for the MySQL query below?

The execution of the following MySQL query often takes 2-3 minutes. The objective is to select records from a table, where 2 of it's columns' values are also contained in an other, previously created temporary table. This table has only one column. This temporary table is created instead of 2 subqueries, because 4 tables are needed to be joined in order to get the values. The temporary table holds around 40 000 records in general, the values are of type varchar(32) COLLATE 'utf8mb4_bin', the table1 table has 45 000 records.

table1
a     |  varchar(32)
b     |  varchar(32)

temp
name  |  varchar(32)

CREATE TEMPORARY TABLE IF NOT EXISTS temp AS SELECT name FROM names ...;
SELECT a, b 
FROM table1 
WHERE a IN (SELECT name FROM temp) 
  AND b IN (SELECT name FROM temp);

a and b columns of table1 are indexed.

How to improve the execution speed? Is there a more efficient way of doing this?

Upvotes: 0

Views: 65

Answers (1)

Barmar
Barmar

Reputation: 781096

Add an index to the temp table:

ALTER TABLE temp ADD INDEX (name);

Also use JOIN rather than IN. MySQL generally optimizes this better.

SELECT DISTINCT a, b
FROM table1 AS t1
JOIN temp AS t2 ON t1.a = t2.name
JOIN temp AS t3 ON t1.b = t3.name

Upvotes: 1

Related Questions