Try this: [EMAIL PROTECTED]:ule> select * from a; +----+------+ | id | data | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec)
[EMAIL PROTECTED]:ule> select * from b; +----+------+ | id | data | +----+------+ | 1 | aa | | 3 | bb | | 4 | cc | | 3 | bb | +----+------+ 4 rows in set (0.00 sec) [EMAIL PROTECTED]:ule> select A, a.data, b.id as B, b.data FROM (select a.id as A, a.data from a limit 3) a LEFT JOIN b on A=b.id; +---+------+------+------+ | A | data | B | data | +---+------+------+------+ | 1 | a | 1 | aa | | 2 | b | NULL | NULL | | 3 | c | 3 | bb | | 3 | c | 3 | bb | +---+------+------+------+ 4 rows in set (0.00 sec) -- Augusto Bott augusto.bott (at) gmail.com On 2/2/06, Scott Klarenbach <[EMAIL PROTECTED]> wrote: > I have a table `requirement` which is left joining to a table `inventory` > based on a matching `partNumber` column. The inventory table has millions > of records, the requirement table has tens of thousands. I'm noticing that > the left join between requirement and inventory doesn't take advantage of a > LIMIT clause. So whether I select all records from requirement or limit it > to 50, the LEFT JOIN operation still seems to be calculating for ALL > requirement records against ALL inventory records. (The query takes the > exact same amount of time, whether I pull 50 requirement records or 10,000). > > How can I force mysql to only join the inventory table for the those 50 > records brought back by the LIMIT clause? > > What I would do in a more powerful DB like SQL Server, is build a temporary > table with my 50 requirement rows, and then perform the inventory join on > the temp table. But due to MySQL SPROC limitations (ie, LIMIT clauses must > have integer constants, not parameters) and View limititations (ie, no > indexing of views), I'd have to build this temporary table and the rest of > query in PHP first, which is really ugly. > > I'm hoping there is a nice SQL trick I can use with MySQL to restrict the > join to only those records that would come back from the limit set. > > Thanks, > Scott Klarenbach > >
