combo indexes, subqueries, and optimization question...

[Leeoniya]

Hello, I'd like to see how to properly design indexes.

For example lets take:

SELECT
id,
name,
category
FROM
products
WHERE
category = 'toys' AND
name LIKE '%soft%'

indecies on both category and name would of course help the situation...but say i had only 10 categories but 40,000 records....would it help to do a subquery to select the categories first and then select names from that, or is the optimizer smart enough to figure this out?

what would be a situation where a combination index would yield better performance than a single index on each needed column?

thanks,
Leon

[Fumigator]

Yes, MySQL will typically find the best access path without having to code strange things in your queries.

The best thing to do is run EXPLAIN (http://dev.mysql.com/doc/refman/5.0/en/using-explain.html) on the query and analyse the results. Query optimization is voodoo magic, and while there are some general rules you can follow, each query should be EXPLAINed and evaluated.