Is there a way of changing what it believes? The resultset size is only a few thousand lines.
And, btw, why does it believe that a table scan is more efficient than using an index? Jack -----Original Message----- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 12:24 PM To: Jack Coxen; MySQL List (E-mail) Subject: Re: Index not functioning The optimizer is informing you that `it` believes a table scan is more efficient than using an index due to the resultset size. On Wed, 24 Mar 2004, Jack Coxen wrote: > > I have a series of tables that were created by: > > CREATE TABLE ifInOctets ( > id int(11) NOT NULL default '0', > dtime datetime NOT NULL default '0000-00-00 00:00:00', > counter bigint(20) NOT NULL default '0', > KEY ifInOctets_idx (dtime) > ); > > When I run a query against any of the tables, the index isn't used. The > query syntax is: > > SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 WHERE > id=2809 AND dtime>FROM_UNIXTIME(1073970000) AND > dtime<=FROM_UNIXTIME(1076734799) ORDER BY dtime; > > Running an EXPLAIN of that command gives: > > > +----------------+------+---------------+------+---------+------+------- > --+- > ----------------------------+ > | table | type | possible_keys | key | key_len | ref > | > rows | Extra | > > +----------------+------+---------------+------+---------+------+------- > --+- > ----------------------------+ > | ifInOctets_137 | ALL | dtime | NULL | NULL | NULL > | > 9279150 | Using where; Using filesort | > > +----------------+------+---------------+------+---------+------+------- > --+- > ----------------------------+ > 1 row in set (0.00 sec) > > I'm running on a Sun E250 w/RAID running Solaris 8. I'm running MySQL > 4.0.16 precompiled Solaris binary. My my.cnf is essentially the > my-huge.cnf > file. Other people are running this application (RTG - > http://rtg.sourceforge.net) on various platforms and MySQL versions and > ARE > NOT having this problem. > > I've run ANALYZE and OPTIMIZE against the tables with no effect. I've > dropped the indexes and recreated them with no effect. I've done > everything > I can think of with no effect. I am now officially stumped. > > Does anyone have any suggestions on what is going on and how I can fix > it? > Any help would be greatly appreciated. > > Thanks, > > Jack > > Jack Coxen > IP Network Engineer > TelCove > 712 North Main Street > Coudersport, PA 16915 > 814-260-2705 > >
