Multi-column join very slow in MySQL
<p>I have two large tables, <code>table_1</code> and <code>table_2</code>. Each has columns <code>id</code>, <code>name</code> and <code>number</code>. I want to find the records in <code>table_1</code> that have matching names and numbers in <code>table_2</code>: </p>

<pre><code>select t1.* from table_1 t1, table_2 t2 
where t1.name = t2.name and t1.number = t2.number;
</code></pre>

<p>Or using the join syntax: </p>

<pre><code>select t1.* from table_1 join table_2 on
t1.name = t2.name and t1.number = t2.number;
</code></pre>

<p>Both are very slow. </p>

<p>I added indexes for <code>name</code> and <code>number</code> in both tables. Still very slow. I then added a multi column index <code>(name, number)</code> on both tables. Still very slow.</p>

<p>Finally, I tried creating a <code>combined</code> column in both tables and set its value to <code>concat(name, number)</code>. I added indexes in both tables for the <code>combined</code> column and then ran the following: </p>

<pre><code>select t1.* from table_1 t1, table_2 t2
where t1.combined = t2.combined
</code></pre>

<p>Super fast!</p>

<p>Isn't there a more elegant way to achieve the performance without the awkward workaround of creating the <code>combined</code> column?</p>