On 11 Sep 2003 at 15:31, Tongprasith, Anan wrote: > 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?
Yes, 'USE INDEX (index_name)' in the SELECT statement after the table name should do it, though MySQL still won't use the index if it's not useful. > 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. I think you may be confused about indexes. Whether an index is being used or not has no effect on the number of rows returned, just on how fast they're returned. But maybe I'm misunderstanding. Can you post the output of SHOW CREATE TABLE for your table and EXPLAIN for your queries? The answer to your initial question is that, yes, the optimizer is affected by the data in the table. It's not a bug. Otherwise it wouldn't be mouch of an optimizer. It still does make mistakes, though, and USE INDEX should solve the problem in those cases. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
