just out of curiousity, have you tried the 4.1.1 version? there may (or may not) be something from 4.1.0 to 4.1.1 could be a total waste of time, but you never know.
Dan. On Mon, 29 Mar 2004, Jack Coxen wrote: > 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: > ---------------------------------------- > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
