MySQL LEFT JOIN slow, no index used

Question

So we've got two tables in two databases, db1 and table1, and db2 and table2. However, trying to do a LEFT JOIN on them takes upwards of three minutes. Insane, I know.

SELECT * FROM `db1`.`table1` AS a LEFT JOIN `db2`.`table2` AS b ON a.col=b.col

a is about 14,000 rows. b is about 825,000 rows.

We have col of both a and b indexed. col is, in both cases, a VARCHAR(16) with latin1_swedish_ci collation.

Running an explain

EXPLAIN SELECT * FROM `db1`.`table1` AS a LEFT JOIN `db2`.`table2` AS b ON a.col=b.col

shows us that no index is being used for either table. I assume this is why the performance is so terrible.

1   SIMPLE  a   ALL NULL    NULL    NULL    NULL    14174   
1   SIMPLE  b   ALL NULL    NULL    NULL    NULL    824687  

However, I don't understand why this is happening or how to fix it. Joining b with other tables that are far larger works in seconds, and indexing is appropriately used. What is happening in this case, and what steps should I take to correct it?

Answer 1

You're selecting every column from every table. Varchar fields are inefficient for indexing oppose to a primary key index. Your index should be on the fields you're joining on otherwise, they are pretty useless, except for search efficiency.

SELECT col, col4, col5, col6 FROM `db1`.`table1` AS a LEFT JOIN (SELECT col,col1, col2, col3 from `db2`.`table2` order by 1,2,3) AS b ON a.col=b.col

If you join on the sub-query you will only extract the columns you need which will speed up performance dramatically, as well as you create an index with your order by 1,2,3 at the end of the sub-query.

You must include the field you will be joining on in the sub-query also...

ANSWER

It will not make sense to use the index for your query, because you are selecting the full text column. That means that MySQL cannot use the index alone to satisfy the query.