>>>>> "Dan" == Dan Nelson <dnelson@stripped> writes:
Dan> In the last episode (Oct 27), Bob Kline said:
>> On Wed, 27 Oct 1999, Mark Alliban wrote:
>> > I have a table with about 1,000,000 rows. There is an EMUN('Y','N')
>> > column called RecordComplete to say whether the row is finished or
>> > not. At any time only about 20 are 'N' and the rest are 'Y'. This
>> > column is indexed. There is also a column AccountNo (DECIMAL(12,0),
>> > which shows the account for the record. This is also indexed.
>> >
>> > When I perform a query specifying both RecordComplete='N' and
>> > AccountNo=12345, it uses the index on AccountNo, which takes much
>> > longer than the index on RecordComplete. Is there any way to force a
>> > query to use a certain index?
>>
>> Have you tried isamchk --analyze on the table? (Backup first, please.)
Dan> And after you've analyzed, post the "explain select" output of both
Dan> queries.
Dan> Analyzing might not work in this case, though. I don't think mysql's
Dan> analyze option records a frequency histogram of the keys; it just
Dan> stores average records/key (in this case 500,000 for the enum field,
Dan> and probably between 1 and 100 for AccountNo).
Hi!
This above assumtion is right.
On the other hand, MySQL only uses the average records/key when you
are searching with a reference from another table. In your examples,
you are searching on constants, in which case MySQL will use the index
information to quickly determinate approximately how many matching keys
for a value. In most cases the index is about as accurate enough to
let MySQL use the optimal key for a query.
Regards,
Monty
