CODEWITHSUNDEEP
mysqljoinwhere-clauseindexing
René Herzog
René Herzog

Reputation: 31

Mysql not using index in LEFT JOIN when joined column used in WHERE clause

I've been puzzling around this problem in mySQL 5.0.51a for quite a while now:

When using LEFT JOIN to join a table AND using a column of the joined table in the WHERE clause, mySQL fails to use the primary index of the joined table in the JOIN, even FORCE INDEX (PRIMARY) fails.

Yet I need both of them.

Faulty: (in my special case up to 1000 secs of exec time)

SELECT *
FROM tbl_contract co
LEFT JOIN tbl_customer cu ON cu.customer_id = co.customer_id
WHERE cu.marketing_allowed = 1 AND co.marketing_allowed = 1
GROUP BY cu.id
ORDER BY cu.name ASC

Working, but not solving my problems:

SELECT *
FROM tbl_contract co
LEFT JOIN tbl_customer cu ON cu.customer_id = co.customer_id
GROUP BY co.id

Table structures (transcribed, as the real tables are more complex)

tbl_contract: 
id: INT(11) PRIMARY
customer_id: INT(11)
marketing_allowed: TINYINT(1)

tbl_customer:
customer_id: INT(11) PRIMARY
marketing_allowed: TINYINT(1)

mySQL EXPLAIN notices PRIMARY as possible key when joining, but doesn't use it.

There has been one solution:

SELECT (...)
HAVING cu.marketing_allowed = 1

Solves the problem BUT we use the query in other contexts, where we can only select ONE column in the whole statement, but HAVING needs the marketing_allowed column to be selected in the SELECT-Statement.

I also noticed, that running ANALYZE TABLE on the desired tables will make mySQL 5.5.8 on my local system do the right thing, but I cannot always assure that ANALYZE has been run right before the statement. Anyways, this solution does not work under mySQL 5.0.51a on our productive server. :(

Is there a special rule in mySQL which I didn't notice? Why are LEFT JOIN indexes not used if columns appear in the WHERE clause? Why can't I force them?

Thx in advance,

René

[EDIT]

Thanks to some replies I could optimize the query using an INNER JOIN, but unfortunately, though seeming absolutely fine, mySQL still rejects to use an index when using an ORDER BY clause, as I found out:

SELECT *
FROM tbl_contract co
INNER JOIN tbl_customer cu ON cu.customer_id = co.customer_id AND cu.marketing_allowed = 1
WHERE cu.marketing_allowed = 1
ORDER BY cu.name ASC

If you leave the ORDER BY out, mySQL will use the index correctly. I have removed the GROUP BY as it has no relevance in the example.

[EDIT2]

FORCING Indexes does not help, as well. So, the question is: Why does mySQL not use an index for joining, as the ORDER BY is executed AFTER joining and reducing the result set by a WHERE clause ? This should usually not influence joining...

Upvotes: 3

Views: 7647

Answers (3)

Axtux
Axtux

Reputation: 11

I had the same trouble. MySQL optimizer is not using indexes while using JOIN with conditions. I changed my SQL statement from JOIN to subqueries :

SELECT
    t1.field1,
    t1.field2,
    ...
    (SELECT
        t2.field3
        FROM table2 t2
        WHERE t2.fieldX=t1.fieldX
    ) AS field3,
    (SELECT
        t2.field4
        FROM table2 t2
        WHERE t2.fieldX=t1.fieldX
    ) AS field4,
FROM table1 t1
WHERE t1.fieldZ='valueZ'
ORDER BY t1.sortedField

This request is much more complicated but as indexes are used, it is also much more faster.

You could also use STRAIGHT_JOIN but performance is better with above query. Here's a comparison for by DB with 100k rows in table1 and 20k in table2 :

  • 0.00s using above query
  • 0.10s using STRAIGHT_JOIN
  • 0.30 using JOIN

Upvotes: 1

J A
J A

Reputation: 1766

Have you tried multiple condition on JOIN clause?

SELECT *
FROM tbl_contract co
LEFT JOIN tbl_customer cu ON cu.customer_id = co.customer_id AND cu.marketing_allowed = 1
WHERE co.marketing_allowed = 1

Upvotes: 0

user330315
user330315

Reputation:

I'm not sure I understand what you are asking, but 

SELECT *
FROM tbl_contract co
LEFT JOIN tbl_customer cu ON cu.customer_id = co.customer_id
WHERE cu.marketing_allowed = 1 AND co.marketing_allowed = 1

will not do an outer join (because of cu.marketing_allowed = 1).

You probably meant to use:

SELECT *
FROM tbl_contract co
   LEFT JOIN tbl_customer cu 
        ON cu.customer_id = co.customer_id
       AND cu.marketing_allowed = 1 
WHERE co.marketing_allowed = 1

Upvotes: 1

Related Questions