List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 14 1999 9:45pm
Subject:Re: Specifying indexes
View as plain text  
>>>>> "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).


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.

Specifying indexesMark Alliban27 Oct
  • Re: Specifying indexesBob Kline27 Oct
    • Re: Specifying indexesDan Nelson27 Oct
      • Re: Specifying indexesMichael Widenius15 Nov
Re: Specifying indexesMark Alliban27 Oct
  • Re: Specifying indexesBob Kline27 Oct
Re: Specifying indexesMark Alliban29 Oct
  • Re: Specifying indexesBob Kline29 Oct
  • Re: Specifying indexesMichael Widenius15 Nov
Re: Specifying indexesMark Alliban3 Nov
  • Re: Specifying indexesBob Kline3 Nov