Re: a query not using index
在 2010年11月9日 下午3:51，Johan De Meersman vegiv...@tuxera.be 写道： Indexes typically only work on the left-hand-side. Rewrite as select * from ip_test where startNum = 3061579775 and endNum = 3061579775; Thanks. But this seems the same case happened: mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ And I'm sure the select can fetch records: mysql select * from ip_test where startNum = 3061579775 and endNum = 3061579775; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | Please suggest, thanks again. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). Thanks Aveek On Nov 9, 2010, at 1:21 PM, Johan De Meersman wrote: Indexes typically only work on the left-hand-side. Rewrite as select * from ip_test where startNum = 3061579775 and endNum = 3061579775; Magic will happen. 2010/11/9 wroxdb wro...@gmail.com Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Thanks for the idea. I have changed the datatype to bigint, the result is not changed. mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 在 2010年11月9日 下午5:20，Aveek Misra ave...@yahoo-inc.com 写道： I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Probably indexes need to be rebuilt using myisamchk after you changed the data type of the index columns. Apart from that I can't see why your query is not using the indexes. Is it possible that the cardinality of the column values is so low that indexes are not being used? You could try and run a ANALYZE TABLE (or myismachk -a for MyISAM tables) and then a SHOW INDEX to see the cardinality information for these key columns. Thanks Aveek On Nov 9, 2010, at 3:43 PM, wroxdb wrote: Thanks for the idea. I have changed the datatype to bigint, the result is not changed. mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 在 2010年11月9日 下午5:20，Aveek Misra ave...@yahoo-inc.com 写道： I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
On 11/8/2010 10:47 PM, wroxdb wrote: Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. Have you tried a combined index of (startnum,endnum) instead of two single-column indexes? You may still run into problems, though, because ranged searches are usually performed as WHERE column_A BETWEEN X AND Y and not as WHERE X BETWEEN column_A and column_B and the optimizer has been designed to evaluate the first pattern but not the second. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Would a compound index on both startnum and endnum be a better choice? JW On Tuesday, November 9, 2010, Aveek Misra ave...@yahoo-inc.com wrote: Probably indexes need to be rebuilt using myisamchk after you changed the data type of the index columns. Apart from that I can't see why your query is not using the indexes. Is it possible that the cardinality of the column values is so low that indexes are not being used? You could try and run a ANALYZE TABLE (or myismachk -a for MyISAM tables) and then a SHOW INDEX to see the cardinality information for these key columns. Thanks Aveek On Nov 9, 2010, at 3:43 PM, wroxdb wrote: Thanks for the idea. I have changed the datatype to bigint, the result is not changed. mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 在 2010年11月9日 下午5:20，Aveek Misra ave...@yahoo-inc.com 写道： I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
a query not using index
Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Indexes typically only work on the left-hand-side. Rewrite as select * from ip_test where startNum = 3061579775 and endNum = 3061579775; Magic will happen. 2010/11/9 wroxdb wro...@gmail.com Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Why is simple query not using index?
On Tue, Mar 3, 2009 at 6:51 PM, Perrin Harkins per...@elem.com wrote: My guess would be that your table is too small to bother using an index on. There's some information in the MySQL docs about when it chooses to use an index. For small tables, using one makes the query slower. I think this is likely the key point. When I changed the query to: explain select count(*) from member force index (expiration) where expiration date_add(CURDATE(), interval 30 day) it gave me this: idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1|SIMPLE|member|range|expiration|expiration|4|null|26|Using where; Using index This used the index, and the number of rows addressed is the correct number of rows. I found information in the MySQL docs (7.4.5. How MySQL Uses Indexes) that says it might not use an index if it determines that a table scan would be less expensive, but nothing that says specifically when this would happen (which doesn't surprise me). - Perrin On Tue, Mar 3, 2009 at 7:58 PM, David Karr davidmichaelk...@gmail.com wrote: I'm using MySQL 5.0.67-0ubuntu6. I'm stepping through MySQL - 4th Edition. There's a simple table called member that we've just added an index to, for the expiration column, which is a date column. The current example in the book is: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: range possible_keys: expiration key: expiration key_len: 4 ref: NULL rows: 6 Extra: Using where Unfortunately, that's not the output I'm getting. It's actually this: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: all possible_keys: expiration key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where Copying the index info from SQuirreL, it is: INDEX_QUALIFIERINDEX_NAMEORDINAL_POSITIONCOLUMN_NAME ASC_OR_DESCNON_UNIQUETYPECARDINALITYPAGES FILTER_CONDITION |expiration|1|expiration|A|true|3|102|0|null It's a bit hard to read, but I replaced tab characters with | between each column. Why might this query not be behaving as I expect?
Why is simple query not using index?
I'm using MySQL 5.0.67-0ubuntu6. I'm stepping through MySQL - 4th Edition. There's a simple table called member that we've just added an index to, for the expiration column, which is a date column. The current example in the book is: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: range possible_keys: expiration key: expiration key_len: 4 ref: NULL rows: 6 Extra: Using where Unfortunately, that's not the output I'm getting. It's actually this: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: all possible_keys: expiration key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where Copying the index info from SQuirreL, it is: INDEX_QUALIFIERINDEX_NAMEORDINAL_POSITIONCOLUMN_NAME ASC_OR_DESCNON_UNIQUETYPECARDINALITYPAGES FILTER_CONDITION |expiration|1|expiration|A|true|3|102|0|null It's a bit hard to read, but I replaced tab characters with | between each column. Why might this query not be behaving as I expect?
Re: Why is simple query not using index?
My guess would be that your table is too small to bother using an index on. There's some information in the MySQL docs about when it chooses to use an index. For small tables, using one makes the query slower. - Perrin On Tue, Mar 3, 2009 at 7:58 PM, David Karr davidmichaelk...@gmail.com wrote: I'm using MySQL 5.0.67-0ubuntu6. I'm stepping through MySQL - 4th Edition. There's a simple table called member that we've just added an index to, for the expiration column, which is a date column. The current example in the book is: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: range possible_keys: expiration key: expiration key_len: 4 ref: NULL rows: 6 Extra: Using where Unfortunately, that's not the output I'm getting. It's actually this: mysql EXPLAIN SELECT * FROM MEMBER - WHERE expiration DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *** 1. row *** id: 1 select_type: SIMPLE table: MEMBER type: all possible_keys: expiration key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where Copying the index info from SQuirreL, it is: INDEX_QUALIFIER INDEX_NAME ORDINAL_POSITION COLUMN_NAME ASC_OR_DESC NON_UNIQUE TYPE CARDINALITY PAGES FILTER_CONDITION |expiration|1|expiration|A|true|3|102|0|null It's a bit hard to read, but I replaced tab characters with | between each column. Why might this query not be behaving as I expect? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slow query not using index
Bad news: I have a slow query that doesn't appear to be using an index even if I force it. Good news: the forehead shaped dent in my desk is really progressing well. Here's the query: SELECT DISTINCT poster_data.* FROM poster_data, poster_prodcat, poster_categories WHERE poster_categories.apcatname LIKE '%Celebrity Name%' AND poster_prodcat.apcatnum = poster_categories.apcatnum AND poster_data.apnumber = poster_prodcat.apnumber ORDER BY poster_data.aptitle poster_data has 61,343 rows of unique data for each apnumber (poster) poster_categories has 26,716 rows...a category id and a category name poster_prodcat has 993,410 rows...a category id and the apnumber (poster) My query is supposed to find all the posters that are from categories that contain a celebrity name. Here's the slow entry: # Query_time: 10 Lock_time: 0 Rows_sent: 30 Rows_examined: 1271071 So it's examining over a million rows to send 30. When I do an explain: Table: poster_data Type: ALL Possible Keys: posterid Key: NULL Key_len: NULL Rows: 61479 Extra: Using temporary; Using filesort Table: poster_prodcat Type: ref Possible Keys: prodcat_ind,apcatnum,apnumber Key: apnumber Key_Len: 3 ref: poster_data.apnumber Rows: 12 Extra: Distinct Table: poster_categories Type: eq_ref Possible Keys: PRIMARY Key: PRIMARY Key_len: 4 Ref: poster_prodcat.apcatnum Rows: 1 Extra: Using where; Distinct Even if I force the use of posterid in the poster_data table, it still doesn't use it. Essentially the query's asking to find category numbers for categories that contain the name, use those numbers to find the poster id's that match those category numbers and then return the poster info. Thanks for any insight. -- Ian Evans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query not using index
Ian M. Evans wrote: Bad news: I have a slow query that doesn't appear to be using an index even if I force it. Good news: the forehead shaped dent in my desk is really progressing well. Here's the query: SELECT DISTINCT poster_data.* FROM poster_data, poster_prodcat, poster_categories WHERE poster_categories.apcatname LIKE '%Celebrity Name%' AND poster_prodcat.apcatnum = poster_categories.apcatnum AND poster_data.apnumber = poster_prodcat.apnumber ORDER BY poster_data.aptitle poster_data has 61,343 rows of unique data for each apnumber (poster) poster_categories has 26,716 rows...a category id and a category name poster_prodcat has 993,410 rows...a category id and the apnumber (poster) My query is supposed to find all the posters that are from categories that contain a celebrity name. Here's the slow entry: # Query_time: 10 Lock_time: 0 Rows_sent: 30 Rows_examined: 1271071 So it's examining over a million rows to send 30. When I do an explain: Table: poster_data Type: ALL Possible Keys: posterid Key: NULL Key_len: NULL Rows: 61479 Extra: Using temporary; Using filesort It's having to check each poster_data row to make sure it's distinct. If you remove the distinct is it fast? If the data in that table is unique, why do you need the distinct? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query not using index
One of the list readers (thanks Brent!) suggested using a full text index on the category names field. Queries dropped from 10-49 seconds down to 0.0085 Thanks for the emails folks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
