Bug #42732 LEFT JOIN Authors a on b.AuthorsId = a.Id AND sex = 'F'
Submitted: 10 Feb 2009 15:00 Modified: 10 Feb 2009 20:03
Reporter: Paul van Keulen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S5 (Performance)
Version:5.1 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: LEFT JOIN AND

[10 Feb 2009 15:00] Paul van Keulen
Description:
Hello,

I have serious performance issue when i execute a query with an AND statement in  the left join part of a query. Example:
Table1 Books
Id int
Name varchar
AuthorId int

Table2 Authors
Id int
Name varchar
Sex varchar

Next I use the following query
SELECT Id, Name
FROM Books b
LEFT JOIN Authors a ON b.AuthorId = a.Id AND Sex = 'F';

Why does this statement perform poor? Is there a way to optimize this?

How to repeat:
Table1 Books
Id int
Name varchar
AuthorId int

Table2 Authors
Id int
Name varchar
Sex varchar

Next I use the following query
SELECT Id, Name
FROM Books b
LEFT JOIN Authors a ON b.AuthorId = a.Id AND Sex = 'F';
[10 Feb 2009 20:03] Shane Bester
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

please check http://dev.mysql.com/doc/refman/5.1/en/using-explain.html for more info.