MySQL Forums
Forum List  »  Performance

Re: Slow(er) Query Response
Posted by: R Rothe
Date: November 12, 2004 02:45PM

Some clarifications to my original post:

The index being used is key'd on msid (int) ,msgnum (bigint). It is called "groupid".

When my where clause checks only for equality as in:

where msid=25 and msgnum=300000

EXPLAIN tells me that it is using the entire key length of 12.

If my where clause contains a range as in:

where msid=25 and msgnum between 300000 and 500000

EXPLAIN only uses the portion of the key applicable to msid and SCANS to find the starting place to return data.

This is presenting a huge performance problem for me since the higher the starting msgnum in the where clause, the longer the query takes to return. Is there no way to force MYSQL to use the index to find the starting position for the result set?

By the way, I've tried this on both MYISAM and INNODB and the result is exactly the same. MSGNUM is, by the way, assigned sequentially so the server may think it doesn't need an index --- the problem seems to be locating the first record of the result set (remember, I'm using mysql_use_result so the result set is not buffered).

Thanks again.


Options: ReplyQuote

Written By
November 11, 2004 03:48PM
November 11, 2004 07:02PM
November 11, 2004 07:14PM
November 11, 2004 08:13PM
November 12, 2004 07:26AM
Re: Slow(er) Query Response
November 12, 2004 02:45PM
November 16, 2004 07:34PM

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.