Joe, ----- Original Message ----- From: ""Joe Smith"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, December 23, 2002 6:41 AM Subject: Innodb querry optimizer suddenly making TERRIBLE choices? Urgent, Please help!
> I'm in a bit of a tough spot -- My innodb table of WebActionLogs (which > has 1 million rows spread out over the last year and a half) has suddenly > stopped using 'TimeInitiated' to select only the last 24 hours of data > (usually less than 7,500 rows), and now wants to copy all 1 million rows to > a temporary table no matter what I do! I tried adding a 'USE INDEX > (TimeInitiated_ind)', it STILL does the longest possible query by copying > everything to a temporary table first. > > Suddenly hundreds of queries that were working fine yesterday and ran under > 0.1 seconds are taking 40 to 50 seconds and ending up in the slow query log. > This has been working fine for months. > > Is there a way to FORCE innodb to use the index whethere it thinks it's > faster or not? I know it will be 400x faster if it does but USE INDEX > apparently has no effect. unfortunately USE INDEX has a a bit misleading name. It only bans MySQL from using any OTHER index than the ones listed in the argument, but it does not ban MySQL from using a table scan. We have talked that Monty should introduce a new clause FORCE INDEX(indexname) which would ban table scans. ... > +-------------+ > | VERSION() | > +-------------+ > | 3.23.51-log | > +-------------+ > > Thanks in advance for any clues! Please upgrade to 3.23.54b http://www.mysql.com/downloads/mysql-max-3.23.html. I have tuned the optimizer to favor index searches over table scans. > Joe Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
