List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 19 1999 8:02pm
Subject:Re: select doesn't seem to be using index
View as plain text  
>>>>> "a" == a rancid amoeba <rancid@stripped> writes:

>> I never encountered this (for me it always worked), so I cannot really
>> comment on this. I can only question whether you maybe made a mistake:
>> - you did stop mysqld while running isamchk?
>> - you did: isamchk -a your-data-directory-path/RecordLabel.ISM?
>> (or *.ISM)
>> Would you please try again and and quote the output. Thanks.
>> 


a> I didn't realize that I needed to stop mysqld before running isamchk (major
a> bummer too because once I shut the server down it's damn near impossible to
a> get it running again...for some reason safe_mysqld hangs for an eternity on
a> starting the server but re-running mysql_install_db starts it up just fine
a> but then all my permissions have been blown...sometimes I just want to get
a> a divorce from my computer).

If you are running isamchk -r, you must at least somehow ensure that no
one is using the table that you are repairing during the repair.
Before the repair you should also force mysqld to close the file with
'mysqladmin flush-tables'

a> So I ran isamchk like this:

>> isamchk -a RecordLabel.ISM
a> Checking ISAM file: RecordLabel.ISM
a> Data records:     257   Deleted blocks:       0
a> - check file-size
a> - check delete-chain
a> - check index reference
a> - check data record references index: 1
a> - check data record references index: 2
a> - check data record references index: 3
a> - check data record references index: 4
a> - check record links

<cut>

mysql> explain SELECT r.LabelID, r.LabelName, r.URL, r.LocationID, ll.City,
> ll.State_Prov, ll.Country, g.Genre, g.GenreGIF
-> FROM RecordLabel r
-> LEFT JOIN LabelLocation ll ON r.LocationID = ll.LocationID
-> LEFT JOIN GenreCode gc ON r.LabelID=gc.LabelID
-> LEFT JOIN Genre g ON gc.GenreNumber=g.GenreNumber 
-> WHERE AlphaOrder LIKE 'c%'
-> ORDER BY r.AlphaOrder, r.LabelID;
a>
> +-------+--------+-------------------------+---------+---------+----------------+------+------------+
a> | table | type   | possible_keys           | key     | key_len | ref            |
> rows | Extra      |
a>
> +-------+--------+-------------------------+---------+---------+----------------+------+------------+
a> | r     | ALL    | AlphaOrder,AlphaOrder_2 | NULL    |    NULL | NULL           | 
> 257 | where used |
a> | ll    | eq_ref | PRIMARY                 | PRIMARY |       4 | r.LocationID   |   
> 1 |            |
a> | gc    | ref    | LabelID,PRIMARY         | PRIMARY |       4 | r.LabelID      |   
> 1 |            |
a> | g     | eq_ref | PRIMARY                 | PRIMARY |       2 | gc.GenreNumber |   
> 1 |            |
a>
> +-------+--------+-------------------------+---------+---------+----------------+------+------------+


Monty> The above means that MySQL considered using the index of AlphaOrder
Monty> for the 'r' table, but concluded that it will be faster to scan the
Monty> table than use the index.  How many rows it it that matches
Monty> AlphaOrder LIKE 'c%' ?

a> For the letter C it returns 200 of the 257 rows (I'm using a small subset of
a> the data so I don't have to scroll through tons of output while testing).
a> If I do the same SELECT but on the letter B which currently would only match
a> 13 rows I get:

<cut>

a> So, in spite of the NULLs listed above, am I to gather that once there
a> are thousands of rows the index will kick in?

Yes.

As scanning index is by the average 3 times slower than scanning rows, its
better to scan rows in this case!

Regards,
Monty
Thread
select doesn't seem to be using indexa rancid amoeba18 Sep
  • Re: select doesn't seem to be using indexPaul DuBois18 Sep
  • Re: select doesn't seem to be using indexBenjamin Pflugmann18 Sep
    • Re: select doesn't seem to be using indexMartin Ramsch18 Sep
    • Re: select doesn't seem to be using indexa rancid amoeba18 Sep
      • Re: select doesn't seem to be using indexBenjamin Pflugmann19 Sep
        • Re: select doesn't seem to be using indexa rancid amoeba19 Sep
          • Re: select doesn't seem to be using indexMichael Widenius20 Sep
        • similar question plus ORDER BY (was Re: select doesn't seem to be using index)AAron nAAs20 Sep
      • Re: select doesn't seem to be using indexMichael Widenius19 Sep