I think you are right. I try adding and deleting data one by one and found no 
particular data that will always "turn off" index in all circumstances. myisamchk 
--analyze doesn't help. 
Is there a way to force using index? I don't think optimizer is smart enough. My table 
has more than 30,000 rows. The query returns only 6,000 rows. 
I modify the query's "where clause" to use indexed column only (to see the ratio 
between hit and miss on index). It returns 7,000 rows. Yet MySQL refuses to use index.
I tried "use index" in the query and it didn't help.

Anan T. 


-----Original Message-----
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 11, 2003 2:18 PM
To: Tongprasith, Anan
Cc: [EMAIL PROTECTED]
Subject: Re: Query optimizer decision to use index depends on data?


I don't think so. Based on what I've read, MySQL always assumes and 
equal distribution of data for an index. Based on this assumption it 
may determine that it's just not worth it to use the index, like if 
your search will return most of the records. You should periodically 
optimize your indexes, at which time MySQL will reevaluate the 
distribution of data and thus it's optimization rules.
You can optimize you indexes with the myisamchk command and the 
--analyze parameter. If you are using InnoDB tables, I don't think 
there is a way to do this.

On Thursday, September 11, 2003, at 01:30 PM, Tongprasith, Anan wrote:

> I have two tables which are exactly the same but have different set of 
> data on them. I wrote a query and used EXPLAIN to see how it would run 
> on each table.
> It turn out that the same query will use index on one table but not 
> the other. So I delete all data on the "bad" table (the one that won't 
> use index) and copy data from the "good" table over.
> Now it says it will use index. Is this a bug?
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to