I'm using the query below to fetch the lowest priced row(s) matching the LIKE.
Suppliers
id | name
----------------------------------------
1 | sup1
2 | sup2
Prices
id | article_number | price | supplier_id
------------------------------------------------
1 | 12345678 | 312 | 1
2 | 12345678 | 219 | 2
3 | 87654321 | 312 | 1
select prices.* from prices
inner join
(
select min(price) as price,
article_number as article_number from prices group by article_number
)
min_prices on prices.price = min_prices.price
and prices.article_number = min_prices.article_number
WHERE prices.article_number LIKE '".$q."%'
Although now also want to fetch the suppliers.name from Suppliers-table:
select prices.*, suppliers.name from prices, suppliers
inner join
(
select min(price) as price,
prices.article_number as article_number from prices group by prices.article_number
)
min_prices on price = min_prices.price
and article_number = min_prices.article_number
WHERE
prices.article_number LIKE '".$q."%' AND
prices.supplier_id = suppliers.id"
This returns 18-times the amount of rows it is supposed to...?
Also the table consists of 10+ millions rows, so efficiency is very important.
For performance, add index on article_number, and use prices.article_number LIKE '".$q."%' in sub query. You'd better use JOIN not , (this is old way) to combine two tables. Like this:
select prices.*, suppliers.name
from prices
inner join (
select min(price) as price, prices.article_number as article_number
from prices
where prices.article_number like '".$q."%'
group by prices.article_number
) min_prices
on price = min_prices.price
and article_number = min_prices.article_number
inner join suppliers
on prices.supplier_id = suppliers.id
where prices.article_number like '".$q."%'