Indexing a SQL table for efficient queries

October 15, 2008 9:31 PM

I have a MySQL table with about 100,000 items in it. Question about setting up indexes for efficient querying.

I already have an index set up like this (to choose some random names):

Index type: INDEX
-> field_12
-> field_7
-> field_9

And it works well. However there is now a wrinkle...Field_20 has been added, and it is paired with field_12 in an "or" statement in all queries now. Before the query was:

IF field_12='foo' AND field_7='bar' AND ...

Now it is:

IF (field_12='foo' OR field_20='meh') AND field_7='bar' AND ...

Do I need to re-index the table to reflect that, and if so, how?

posted by maxwelton to computers & internet (8 comments total)

Oops. If it matters, the primary key is a separate field, and is a sequential number which is not a factor in these particular queries.
posted by maxwelton at 9:35 PM on October 15, 2008

Dur, too much PHP. Obviously those "IF" are really "WHERE". OK, going to the corner now.
posted by maxwelton at 9:37 PM on October 15, 2008

I'd page orthogonality, since he's the resident MySQL genius, but I think this is the kind of thing the MySQL Query Optimizer looks to take care of with the available indices. Because they're ORs and not joined, I don't think reindexing it any particular way would help.

You can try an EXPLAIN to see if the Optimizer chooses a different index, or changes anything significant.
posted by disillusioned at 9:53 PM on October 15, 2008

You may find that it's best to have indexes on (field_12, field_7) and (field_20, field_7) and construct queries of the form:

SELECT ... WHERE field_12 = 'foo' and field_7 = 'bar'
UNION
SELECT ... WHERE field_20 = 'meh' and field_7 = 'bar'

In essence, break the OR condition into two queries that each taken advantage of their respective indexes and merge the result sets.
posted by SPrintF at 10:22 PM on October 15, 2008

disillusioned writes "I'd page orthogonality,"

Thanks, disillusioned!

disillusiond is right about the explain, and it's dead simple to use in mysql: just put "explain " in front of your query.

SPrintF is half right: a disjunction (an "or") pretty much breaks a query into two queies, and under the covers, that's much like a union. But since the database will do it for you, in most cases doing it explicitly as SPrintF suggests won't buy you anything.

But he is right that the "or" means you have "two" queries and so you need two indices, and about what indices you need.

In addition to your existing index, whatever it is (it's much easier to help you if you show an actual query, rather than a query "kind of like" the actual one, btw), add another that copies it but replaces "foo" with "meh". So if the existing index is on (bar, foo), add one on (bar, meh).

Then run the explain, and see if it's using the indices. You'll probably see index, temp table, index, (temp) table sort.
posted by orthogonality at 10:46 PM on October 15, 2008 [1 favorite has favorites]

Explain showed that even with separate indexes, the "OR" statement had MySQL ignoring them. With separate indexes and the UNION it used the indexes.

Though now you have me wondering, and I need to go test again.
posted by maxwelton at 11:04 PM on October 15, 2008

You know, I should probably just take up making sand candles or rubber-tire sandals or something. You're both right, I must not have had my index indexed properly or something.
posted by maxwelton at 11:12 PM on October 15, 2008

Finally, thanks!
posted by maxwelton at 11:12 PM on October 15, 2008