MySQL Forums
Forum List  »  Optimizer & Parser

Use two indexes (filter "temporary results")
Posted by: Zoltán Lehóczky
Date: December 21, 2008 02:06PM

Hi all!

I have an "Entries" table like this (of course with additional fields and primary index, but only this is regarding my question):
- TopicId int(11) (the Primary of the topic table, tells in which topic the entry is located, but no join here)
- HeaderNumber int(8) (a local counter in a topic, starts in every topic from 1, the second entry is 2...)

All these two are KEY type indexes.

I would like to select entries for a specific page in a specific topic, so I'm using a range of HeaderNumber. Let's say, 15 entries are on a page, and this is for the 2nd page:

SELECT FROM Entries WHERE TopicId=5 AND HeaderNumber BETWEEN 16 AND 30

My problem is, that MySQL chooses to use only HeaderNumber as index, therefore it scans through all records in the table where it is between 16 and 30, disregarding that the query started with a filtering clause for a specific topic. Is there a way to let MySQL use both indexes? I've read that using multiple indexes is supported from version 5, but not in all cases.

However, could it be that MySQL is simply wiser than me? (Of course :-)) Because there are lesser records with HeaderNumber between 16 and 30 than there are with TopicId 5! Anyway, theoretically wouldn't it be faster to first use the index TopicId -> we have 6 thousand records left from 50 thousand; then use HeaderNumber on the result -> we go to number 16 instantly, and read till 30; than only use one index?

Thank you in advance for every hint!

Options: ReplyQuote


Subject
Views
Written By
Posted
Use two indexes (filter "temporary results")
4237
December 21, 2008 02:06PM


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.