MySQL Forums
Forum List  »  Newbie

Re: Opinions on this schema please =)
Posted by: Rick James
Date: February 21, 2009 05:40PM

Here's another way to look at the data and the performance.

Will anyone ever search for simply "rim_width between 7 and 9" ? I doubt it -- they are more likely to say "product = 'wheel' AND rim_width...". That is, there is a pecking order to the search terms. (More in a moment.)

With generic key-value, the engine will
1. rummage around to decide which key is most useful to start with;
2. (logically) fetches all the records that match that one key-value;
3. reach for another attribute and filter on it;
4. etc, until finished.

Note: It can't really take all the criteria into account at once.

With a multi-column index, you must use the first (or first few) columns in the index.

With many 1-column indexes, you are back to the one-step-at-a-time of key-value. (Note: 5.1 may do "merge index" to partially use more than one index.)

Where am I headed? No matter how you structure the data and the indexes, the search will be inefficient, except...

Except when you have exactly the right index. This means all the WHERE thingies, plus any GROUP BY, plus any ORDER BY all in a single index in the right order.

Ok, that's impossible in your generic situation. But let's not give up...

What can be done?
1. _quickly_ eliminate _most_ of the rows.
2. Don't worry about the effort it takes to finish the query -- the number of rows will be (based on step 1) few enough so that even "no index" will be "good enough".

Notice how that web site requires you to pick one of 18 "products" -- that eliminates most of the rows. After that, it has a few breakdowns of what you have to pick next. Possibly the breakdowns are pre-calculated, not done live.

Once you are down to the rim_width, it is almost easier to have your UI do the filtering.

Have fun. Turn on the slowlog and keep an eye on it. Capture the exact SELECT statements created, and run EXPLAIN on them.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.