Mysql does not use the index in LEFT JOIN when the joined column is used in the 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.

• If no column of the joined table is in the WHERE clause, everything works fine.
• If the GROUP BY is removed, the index is also used.

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...

---

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