I'm not sure what your data structure is like, a single table with one column for each attribute or one record for each associated attribute.
If you have the single table structure, then your query may look something like this:
SELECT productName, (if(height IS NOT NULL,1,0)+if(weight IS NOT NULL,1,0)+if(colour IS NOT NULL,1,0)+if(length IS NOT NULL,1,0)) as AttrCnt
FROM products
WHERE weight>100 OR height>30 OR...
ORDER BY AttrCnt DESC
Depending on your version of MySQL, the OR statements may actually cause things to run slower because older version didn't optimize OR statements very well. On the other hand, query caching could considerably speed things up on subsequent queries. With your 12 queries, there is always going to be a fixed amount of overhead to establish the connection to the database.
If your structure is a separate table with 1 associated record for each attribute of each product (much more flexible more data storage), the query is a little more complicated. You'll need to use SUM, GROUP BY and SORT. But it will still be one query sorted by relevance. If this is your structure, please post it and I (or someone else) will come up with the query.
On Dec 15, 2004, at 7:57 PM, Shane McDonald wrote:
A quick question on the performance of the SELECT statement.
I have a table with a set of 50 products, each product has about 10 attributes associated with it.
The user will select certain aspects of a product (height, weight, colour, length etc.), and many products may display a particular attribute, which is why one select may not give me the results I am after. (I also want to display the top 5 products in order of relevance.)
At the moment, I am making 12 individual SELECT statements in the code and using the results to allocate a rank to each product found in that SELECT. e.g. the first select would be SELECT * from table where weight > 100; and each product in the result would be allocated 1 point - the second search would be for height > 30, and so on.
Is there a problem with doing multiple SELECT statements (I am using php) or would this be common place.
Is there an alternative way of doing this search using the SELECT or some other statement ?
I'm just interested to see if I'm being too demanding on the SQL server, as there is a potential that up to 10 users per minute may use this search.
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
