Geek asks and answers

Mysql does not use index in LEFT JOIN when joining column used in WHERE clause

I discussed this issue for quite some time in mySQL 5.0.51a:

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

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

But I need both.

Faulty: (in my special case, up to 1000 seconds of runtime)

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

Work, but not the solution to 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 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 a possible key when joining, but does not use it.

:

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

, , , SELECT marketing_allowed.

, ANALYZE TABLE mySQL 5.5.8 , , ANALYZE . , mySQL 5.0.51a .: (

mySQL, ? LEFT JOIN , WHERE? ?

,

[EDIT]

INNER JOIN, , , , , , mySQL ORDER BY, :

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

ORDER BY, mySQL . GROUP BY, .

[EDIT2]

FORCING . , : mySQL , ORDER BY WHERE? ...

+3
3

, , ,

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

(- cu.marketing_allowed = 1).

, :

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
+1

. MySQL JOIN . SQL JOIN :

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

, , .

STRAIGHT_JOIN, . DB 100k 1 20k 2:

  • 0.00s,
  • 0.10s STRAIGHT_JOIN
  • 0.30, JOIN
+1

Have you tried several conditions regarding the JOIN offer?

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
0
source

More articles:


All Articles