List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 27 1999 3:05pm
Subject:Re: Specifying indexes
View as plain text  
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.)

And after you've analyzed, post the "explain select" output of both
queries.

Analyzing might not work in this case, though.  I don't think mysql's
analyze option records a frequency histogram of the keys; it just
stores average records/key (in this case 500,000 for the enum field,
and probably between 1 and 100 for AccountNo).

-- 
	Dan Nelson
	dnelson@stripped
Thread
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