List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 5 1999 5:06pm
Subject:Re: Use of indexes when ORDER BY'ing
View as plain text  
Matthias Pigulla wrote:
> Hi list,
> I can't clearly resolve the following question with manual §10.4, but
> I'm sure one of you knows ;-) I'd just like to know wheter MySQL can use
> two different keys for retrieving and ordering data.
> Example: I've got a table of discussion threads, having the properties
> id, forum (the board they belong to) and lastpost. I want to get a list
> of all threads for a certain forum, ordered DESC by lastpost.
> Now, can I have the keys: primary id, key forum, key lastpost to
> successfully (ie with indexes) get the result, or will I have to group
> the second and third key together, ie key (forum, lastpost)?
> Would it make sense to make this key unique by concatenating the id -
> unique (forum, lastpost, id)?
> Thanks a lot in advance,
> Matthias

Hi Matthias

As I understand it, MySQL uses one KEY per joined table and query if there is one spanning
the whole AND clauses of that table.

To your example:
If you want to select and order via key you have to use a key spanning (forum, lastpost).
Making it unique doesn't give you anything concerning speed.

If you are unsure if your keying is correct for a specific query, just use the EXPLAIN


Use of indexes when ORDER BY'ingMatthias Pigulla5 May
  • Re: Use of indexes when ORDER BY'ingChristian Mack5 May