Problem with Mysql index when using "OR" statement

Is there any way how to create an functioning index for开发者_如何学运维 this query and to get rid of "filesort"?

SELECT id, title FROM recipes use index (topcat) where
(topcat='$cid' or topcat2='$cid' or topcat3='$cid') 
and approved='1' ORDER BY id DESC limit 0,10; 

I created index "topcat" ( columns: topcat1+topcat2+topcat3+approved+id) but still ge "Using where; Using filesort".

I can create one more column, lets say, "all_topcats" to store topcat numbers in an array - 1,5,7 and then to run query "... where $cid iIN ()...". But the probem is that in this case "all_topcats" column will be "varchar" but "approved" and "id" columns - int, and index will not be used anyway.

Any ideas? Thanks.

You might improve performance for that query if you reordered the columns in the index:

approved, topcat1, topcat2, topcat3, id

It would be useful to know what the table looks like and why you have three columns named like that. It might be easier to organise a good query if you had a subsidiary table to store the topcat values, with a link back to the main table, but without knowing why you have it set up like that it's hard to know whether that would be sensible.

Can you post the CREATE TABLE?

Edit in response to user message

Your table doesn't sound like it's well-designed. The following design would be better: Add two new tables, Category and Category_Recipe (a cross-referencing table). Category will contain a list of your categories and Category_Recipe will contain two columns, one a foreign key to Category and one a foreign key to the existing Recipe table. A row of Category_Recipe is a statement "this recipe is in this category". You will then be able to very simply write a query that will search for recipes in a given category. You also have the ability to put a recipe in arbitrarily many categories, rather than being limited to 3. Look up "database normalisation" and "foreign keys".