Re: Queries returning large number of rows

[Jan Hansen]

In this particular db, there are actually only two tables, with the one I'm most concerned with being very much the larger. In reality, it's likely that when there are no more than 1,000,000 rows all the indexes will fit well into the buffer. It's when I go much beyond that that things start to get trickier. I'm ordering a machine with a RAID array and 2Gb RAM which should help things somewhat.

As regards using just a single column index, I'm not sure this would help unless I could have it as well as the composite index. I don't know how advisable this duplication of indexes would be. The logic of the application is basically this:

a) See whether there are any rows where current_entry begins with 'ABC'

b) If there are, then execute a whole series of queries for rows where current_entry begins with 'ABC' AND previous_entry begins with 'value1', then 'value2', etc.

The composite index is there to enable b), while also being usable for a).

Is there a better way to do this, I wonder? I've now switched to MySQL 5.1 and am wondering whether the index merge stuff might be of use.