Jack you must have a compound index in your table:
ALTER Table ifInOctets_137 add INDEX i_id_dtime(id,dtime); In your SELECT statement, change "USE INDEX (dtime)" to "USE INDEX (i_id_dtime)" Let us know how it works. David > -----Original Message----- > From: Jack Coxen [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 24, 2004 11:51 AM > To: '[EMAIL PROTECTED]'; MySQL List (E-mail) > Subject: RE: Index not functioning > > > I tried using 'USE INDEX' and 'BETWEEN' but it didn't change anything. > > EXPLAIN SELECT counter, UNIX_TIMESTAMP(dtime) FROM > ifInOctets_137 > USE INDEX (dtime) WHERE id=2809 AND dtime BETWEEN > FROM_UNIXTIME(1073970000) > AND FROM_UNIXTIME(1076734799) ORDER BY dtime; > > +----------------+------+---------------+------+---------+---- > --+---------+- > ----------------------------+ > | 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) > > Is my syntax wrong? > > The resultset size should be around 8640 rows - 5 minute > interval data for > 30 days - 12 X 24 X 30 = 8640 > > -----Original Message----- > From: Victor Pendleton [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 24, 2004 1:22 PM > To: Jack Coxen; MySQL List (E-mail) > Subject: RE: Index not functioning > > > 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]
