Optimizers work on a thirty percent rule. If the resultset will contain thirty percent of the table then a table scan will be performed. If you want to `force` the use of an index you can use the `USE INDEX <index-name>` syntax. You also should look at your query. You have three criteria in your where clause id=2809 dtime>FROM_UNIXTIME(1073970000) dtime<=FROM_UNIXTIME(1076734799) A compound index containing id and dtime should be created. Can you use a BETWEEN statement? WHERE id = 2809 AND ( dtime BETWEEN start AND stop) ... Also what is the sum of your `>` and `<=` resultsets? This value may be larger than you expect.
On Wed, 24 Mar 2004, Jack Coxen wrote: > > 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] > <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 <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 > > > > > > ---------------------------------------- Content-Type: text/html; name="unnamed" Content-Transfer-Encoding: quoted-printable Content-Description: ---------------------------------------- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
