Possible to have a single MySQL query for multiple items?

[wrybread]

I have a PHP page drawing multiple items, each in a single row, and currently each makes a separate MySQL query, and now that the database is huge, this is really slow. For example, if it was a page showing different kinds of pastries we have available, I'm drawing the page like this:

[get details about croissants: SELECT * FROM `descriptions` WHERE `item` LIKE 'croissant' LIMIT 1;]

[draw a picture of a croissant with details obtained from DB]

---------------

[get details about baguettes: SELECT * FROM `descriptions` WHERE `item` LIKE 'baguettes' LIMIT 1;]

[draw a picture of a baguette with details obtained from DB]

---------------

[get details about rolls: SELECT * FROM `descriptions` WHERE `item` LIKE 'rolls' LIMIT 1;]

[draw a picture of a rolls with details obtained from DB]

There's 40 items on each page, and it now takes about 5 seconds to load the page. I'm wondering if there's some clever way to query the DB one time at the top of the page with all my queries? Something like this pseudo code:

SELECT * FROM `descriptions` WHERE `item` LIKE 'croissant' OR  `item` LIKE 'baguettes' OR `item` LIKE 'rolls';

and then iterate through the results?

 

[wrybread]

Nevermind, as is probably obvious from the question I'm an idiot. Solved.

[mac_gyver]

you should have a category table, with id and name columns. this would define the different categories of items. the item table would have id, category_id, name, description, and any other columns needed to define each item. to display the category menu you would query the category table to get the category data that you want in the order that you want it, then loop over the result of this query to produce the category menu. if someone clicks on one of the category menu links, the category id in the link would be used to query for and display the items matching that category.

[gizmola]

It does sound like mac_gyver had the solution.  I'm going to assume that your solution was something like:  "SELECT * FROM descriptions WHERE `item` IN ('rolls',  'baguettes', 'croissant')".   You should have an index on the 'item' column as well, if you don't.

I'm a stickler for data consistency, and perhaps this was just an arbitrary example you created, but if you are going to use plurals (rolls, baguettes) then all of these should be pluralized, and you shouldn't have singulars like 'croissant' or 'donut'.

My main comment, is that there is no benefit to using LIKE unless you are using wildcards.  

LIKE works fine when you actually need it as in:  WHERE name LIKE "sm%" finding "Smith", "Smith Jr." and "Smeely", etc.

If however, you attempt to find a substring as in:

WHERE address LIKE "%main$"

This defeats the ability of the database to use indexing.  The queries will work, but assuming this is the primary criteria, will need to tablescan the entire table looking for addresses that have 'main' somewhere in them.

If the dataset is relatively small, these types of "% ... %" or "% ..." queries are acceptable, but if the dataset is large, you may find you are having significant issues.