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:
----------------------------------------
