MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Bob O'Neill Date:June 7 2005 9:26pm
Subject:Re: max_seeks_for_key in InnoDB
View as plain text  
Thanks for your reply.  I guess the root of my problem is that MySQL is 
making a poor choice of index, which I presume is based on the cardinality 
numbers of each index.  When I run ANALYZE TABLE, these values can fluctuate 
wildly -- between 16 and 26,000, for example.  According to the manual, 
"ANALYZE TABLE counts cardinality by doing 10 random dives into each of the 
index trees."  (this is up from 8 random dives in 4.1.11, which is good!) 
Maybe the accuracy of this measurement decreases as tables reach millions of 
rows?

I would really like to avoid rewriting all of my queries to add USE INDEX 
and STRAIGHT JOIN, since some of them are quite complicated and I would 
prefer to leave the job to MySQL.  My questions are:

1) Can the 10 random dives be made configurable?  I would like to do an 
analyze table with 100 random dives if it would produce a more accurate 
count.
2) Is there some reason that my index trees would not be uniform?  Is there 
anything I can do about this?
3) For InnoDB tables, does it make sense to always keep max_seeks_for_key at 
a  low value (1, 100, ?)

Many thanks in advance,
-Bob

----- Original Message ----- 
From: <mfatene@stripped>
To: "Bob O'Neill" <oneill.bob@stripped>
Cc: <mysql@stripped>
Sent: Friday, June 03, 2005 3:20 PM
Subject: Re: max_seeks_for_key in InnoDB


Hi,
you can use a hint to force specific index usage :
http://lib.hutech.edu.vn/ebookonline/ebook1/0596003064/hpmysql-CHP-5-SECT-4.html

But this is not a good idea since data change and index selectivity can 
become
bad.

Also, if the index scan + the table scan is bigger than a full table scan, 
even
you will prefer FTS.

So, according to selectivity, usage of an index can be a very bad idea. Thsi
depends on how many rows your query retreives among the count(*) of the 
table.


Mathias


Selon Bob O'Neill <oneill.bob@stripped>:

> I am having problems with MySQL inconsistently choosing the wrong index, 
> or
> no index at all, for queries on tables with 20 million rows.  Would it be 
> a
> good idea for me to set max_seeks_for_key to 1 (or something less than 4
> billion), in order to force MySQL to use an index?  We are using InnoDB.
>
> Since InnoDB has clustered indexes, is there ever a good reason for MySQL 
> to
> prefer a table scan?
>
> Thanks,
> -Bob



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/mysql?unsub=1


Thread
max_seeks_for_key in InnoDBBob O'Neill3 Jun
  • Re: max_seeks_for_key in InnoDBmfatene3 Jun
  • Re: max_seeks_for_key in InnoDBBob O'Neill7 Jun