1

I am using INNER JOIN and WHERE with LEFT function to match records by its first 8 chars.

INSERT INTO result SELECT id FROM tableA a
INNER JOIN tableB b ON a.zip=b.zip
WHERE LEFT(a.street,8)=LEFT(b.street,8)

Both a.street and b.street are indexed (partial index 8).

The query didn't finish in 24+ hours. I am wondering is there a problem with indexes or is there a more efficient way to perform this task

user2864740
  • 60,010
  • 15
  • 145
  • 220
user3157679
  • 13
  • 3
  • 2
    Your `WHERE` clause don't use indexes because the predicate is not [sargable](http://en.wikipedia.org/wiki/Sargable). – Hamlet Hakobyan Feb 28 '14 at 20:43
  • @HamletHakobyan It seems *ridiculous* that `LEFT()` cannot be used intrinsically with indices (I can understand `RIGHT()` not being sargable) .. not that I expect too much optimization from MySQL these days, but I hope there would be more to it than that >_ – user2864740 Feb 28 '14 at 20:43
  • Translation of Hamlet's comment: since you're doing a string operation on what's in your WHERE clause, MySQL can't use any indexes you may have on that column since it has to perform that operation on *every single row in the table* to determine if the expression is true. – WWW Feb 28 '14 at 20:43
  • What are the indexes on the tables? How large are the tables? – Gordon Linoff Feb 28 '14 at 20:44
  • add fulltext indexes and use match against – Sam Feb 28 '14 at 20:44
  • Are the `zip` columns indexed? Add the `CREATE TABLE` statements for both tables. – ypercubeᵀᴹ Feb 28 '14 at 20:45
  • Although it doesn't talk about LEFT or SUBSTRING(,0,) explicitly: http://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql – user2864740 Feb 28 '14 at 20:48

2 Answers2

5

Mysql won't use indexes for columns that have a function applied.

Other databases do allow function based indexes.

You could create a column with just the first 8 chars of a.street and b.street and index those and things will be quicker.

Rich Bradshaw
  • 71,795
  • 44
  • 182
  • 241
1

This is your query:

INSERT INTO result
    SELECT id
    FROM tableA a INNER JOIN
         tableB b ON a.zip=b.zip
    WHERE LEFT(a.street,8)=LEFT(b.street,8);

MySQL is not smart enough to use a prefix index with this comparison. It will use a prefix index for like and direct string comparisons. If I assume that id is combine from tableA, then the following may perform better:

INSERT INTO result(id)
    SELECT id
    FROM tableA a
    WHERE exists (select 1
                  from tableB b
                  where a.zip = b.zip and
                        b.street like concat(left(a.street, 8), '%')
                 );

The index that you want is tableB(zip, street(8)) or tableB(zip, street). This may use both components of the index. In any case, it might get better performance even if it cannot use both sides of the index.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786