I have this query, and it appears to work as I want, but I just want to check that is correct and optimal.
The main table concerned is a table of “Categories” for products.
My aim is to select a list of categories, but I only want to list “active” categories, to define what I mean by “active”, that is a category that is set to be “visible” (in its own column) and has one or more products belonging to it which are “in stock” (defined in the product’s column).
Any category which does not meet these requirements should not be selected.
The secondary table concerned is the table of products or items. The link between the tables is a “category” column in the “item” table which relates to the category id.
This is the query I have so far:-
SELECT DISTINCT category.id, category.name, category.description
FROM category
JOIN item ON category.id = item.category
WHERE category.visible = 1 AND item.instock = 1
ORDER BY category.sort, category.name
DISTINCT requires a sort… and then a different ORDER BY requires a second sort
so i would fix that by changing the query to
SELECT DISTINCT
category.sort, category.name, category.description
...
ORDER
BY category.sort, category.name
and the optimizer should skip the re-sort
another approach, which may be even faster, is to use EXISTS because this can be satisfied without retrieving additional rows after the first is found
SELECT category.sort, category.name, category.description
FROM category
WHERE category.visible = 1
AND EXISTS
( SELECT 1
FROM item
WHERE item.category = category.id
AND item.instock = 1
)
ORDER
BY category.sort, category.name
Thank you, that seems to work now I have had the opportunity to try it.
I threw in the DISTINCT to remove the duplicate categories in the result, but it somehow seemed a bit of a hack although it appeared to work.
Regarding performance, to be honest it’s not a huge database or anticipated to be a highly busy site, so I suppose any gains will be splitting hairs in rality. But I still like to think things are being done efficiently and any education in how to make more efficient queries isn’t a bad thing at all.