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
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S5 (Performance)
• Version: 5.1
• OS: Windows
• 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] MySQL Verification Team

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.