Integrating a subquery only for rows retrieved

[bauhsoj]

I have been doing a LEFT JOIN on two tables: items & prices

However, 10's of thousands of results can come back for a simple query resulting in a very slow query time. I found that I can drastically speed this up by doing a search in the items table then searching the prices table for the corresponding id's they share in common that I would have done the LEFT JOIN with but only for the 10 id's returned as a result of LIMIT.

Currently I am doing this with PHP code and two separate queries, but I would like to know if I can make this much cleaner and have MySQL return it all at once just mimicking how the PHP code separates out the queries. In other words, doing just a search for the prices data based only on the items.id returned from searching the items table.

Here is what I am currently doing:

$result = mysql_query("SELECT * FROM items WHERE category = 5");

$items = array();

$ids = array();
while ($row = mysql_fetch_assoc($result)) {
$ids[] = $row['id']
$items[] = $row;
}

mysql_free_result($result);

$result = mysql_query("SELECT * FROM prices WHERE active = 'Y' AND id IN(".implode(", ", $ids).")");

while ($row = mysql_fetch_assoc($result)) {
foreach ($items as $k => $v) {
if ($row['id'] == $v['id']) {
$items[$k][['price'] = $row['price'];
}
}
}

mysql_free_result($result);

[Fumigator]

Yes; you can make that first SELECT query a sub-query in the 2nd SELECT query.

Something like


SELECT p.* FROM prices as p WHERE p.active = 'Y'
AND EXISTS (SELECT 1 FROM items as i WHERE i.id = p.id AND i.category = 5)