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