Indexes

dougmcc1

What would an ideal index look like for the following query?

$query="SELECT field1,field2,field3,field4,field5 FROM table WHERE field6 LIKE '%$value1%' AND field7!=''";

And this query:

$query="SELECT field1,field2,field3,field4,field5 FROM table WHERE field6='$value1' AND field7!=''";

Or this query:

$query="SELECT field1,field2,field3,field4,field5 FROM table WHERE field6='$value1' OR field7='$value2';

Or this one:

$query="SELECT field1,field2,field3,field4,field5 FROM table WHERE field6='$value1';

Would they all use different indexes or would it be best if they used the same one? What if I had other queries that selected different fields? Which field would I put the index on and what other fields do I need to add to the index(es)?

How would mysql know which index to use if many of them had the same fields added to them? Examples would be great.

Thanks.

suntra

You can have many indexes in a table. What you can have only once, is a primary key...

In my table, there is always a column called "id" (or something that ends with "id") and that column is always the first one. It is auto_increment and is a primary key. Then, if I know I'm going to use a certain field in the where clause, I "index" it.

dougmcc1

I already indexed each field individually but from what I've read it's more efficient to have indexes that contain multiple fields if the queries are selecting multiple fields.

NogDog

From your examples, only field6 and field7 would need to be indexed, as they are the only fields used in your where clauses. There would be nothing gained for those queries by indexing the other columns, and adding unnecessary indexes will have a bit of a negative impact on inserts and updates (since a bit more data has to be saved for each indexed column).

liquorvicar

Indexing field6 won't help you at all for the first query (although it will help for the other queries).
Using

LIKE '%...%'

is a very inefficient way of searching. If your database has the potential to reach any reasonable size, you might want to find another way round it. One way is to use MySQL's FULLTEXT functions. This does have it's drawbacks - notably that it's a proprietary MySQL module and not part of the SQL standard. So if you want a fully-portable, fully-scalable solution you might want to look at creating your own word-index.