RE: Index not functioning - update
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 ROWSFILE 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(107397) 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 dtimeFROM_UNIXTIME(107397) 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 '-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 dtimeFROM_UNIXTIME(107397) 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
RE: Index not functioning - update
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 NAMEROWSFILE 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(107397) 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 dtimeFROM_UNIXTIME(107397) 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 '-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 dtimeFROM_UNIXTIME(107397) 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
RE: Index not functioning - update
No, this is a production system. I can't risk running non-production level software. I could try 4.1.1 on my development system but, even if it worked, I still couldn't put that solution into place on the production server. Jack -Original Message- From: dan [mailto:[EMAIL PROTECTED] Sent: Monday, March 29, 2004 12:24 PM To: Jack Coxen Cc: '[EMAIL PROTECTED]'; MySQL List (E-mail) Subject: RE: Index not functioning - update 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 NAMEROWSFILE 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(107397) 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 dtimeFROM_UNIXTIME(107397) 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 '-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 dtimeFROM_UNIXTIME(107397) AND dtime=FROM_UNIXTIME(1076734799) ORDER BY dtime; Running an EXPLAIN
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 '-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 dtimeFROM_UNIXTIME(107397) 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index not functioning
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] 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 '-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 dtimeFROM_UNIXTIME(107397) 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
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 dtimeFROM_UNIXTIME(107397) 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 '-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 dtimeFROM_UNIXTIME(107397) 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]
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(107397) 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 dtimeFROM_UNIXTIME(107397) 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 '-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 dtimeFROM_UNIXTIME(107397) 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:
RE: Index not functioning
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(107397) 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 dtimeFROM_UNIXTIME(107397) 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 '-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 dtimeFROM_UNIXTIME(107397) 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
