OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
Re: how to use index with order by here

From: Rob Wultsch (wultschgmail.com)
Date: Sun Mar 16 2008 - 13:34:43 CDT

On Sun, Mar 16, 2008 at 10:36 AM, Velen <velenbiz-mu.com> wrote:
> I would suggest u use
>
> SELECT *
> FROM messages
> WHERE id_from between 1 and 2
> AND id_to between 1 and 2
> ORDER BY time

That would only be applicable if he mandated that his users only send
messages to other users with user_id's +/- 1 of their own, or they
will break their message search app.

I bet that would go over well.

Even if this were not an issue changing from the IN to BETWEEN does
not help performance. In fact performance is worse. With the index I
had suggested above and using the sample data I created his original
query examines 28 rows, while yours examines 713, and takes several (4
to 6) times longer to retrieve the rows on 5.0.51.

--
Rob Wultsch

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql