A quick update on this. I tried upgrading to the latest production MySQL - 4.0.18 - with no change. So, if it's a bug, it's an unreported and uncorrected bug.
Also, I tried the query against different size tables. If I try the query against ifInOctets_35, the index works. If I try it against ifInOctets_83, the index doesn't work. The files differ as follows: TABLE NAME ROWS FILE SIZE INDEX WORKED? ifInOctets_35 41240 865977 Yes ifInOctets_83 41923 880362 No I have no idea what (if anything) this means but the query works consistently on tables smaller than ifInOctets_35 and consistently doesn't work on tables larger than ifInOctets_83 - at least so far. I know that a compound index of id and dtime would fix the problem but it would also leave me with a non-standard installation. I'll do that if I have to but I'd prefer to fix the existing setup of a single index on dtime. Jack -----Original Message----- From: Jack Coxen [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 1:51 PM 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: ----------------------------------------