Re: Slow(er) Query Response

[R Rothe]

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.

Rob