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: <[EMAIL PROTECTED]>
To: "Bob O'Neill" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
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 <[EMAIL PROTECTED]>:
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
