Re: ORDER BY not using index?

Hi, Your query have to access all rows in `myTable`, thus MySQL optimizer guesses "reading sequentially is faster than working through an index". http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html The case of not using index, * Reading whole myTable.MYD sequentially * Sorting

ORDER BY not using index?

Hi, Can someone perhaps assist with the below... I'm not sure at all why my index aren't being used for the ORDER BY. Currently some 443K records in the table, but this will grow to a good few million. I simply cannot, afford a filesort. mysql> SELECT COUNT(*) FROM myTable; +--+ | COU

Query is not using Index

', SUM(if (px_orders.sales_orders.order_completed_date >= DATE_SUB(CURDATE( ),INTERVAL DAYOFYEAR(CURDATE( ))-1 DAY),px_orders.sales_order_products.paid_amount,0))) as ytd FROM px_orders.sales_order_products LEFT JOIN px_orders.sales_orders ON px_orders.sales_order_products.order_id = px_orders.

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

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

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

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

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 a

Re: a query not using index

在 2010年11月9日 下午3:51，Johan De Meersman 写道： > 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 <= 30615797

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 > Hello, > > I have a query below: > > mysql> select * from ip_test where 3061579775 between startNum and endNum; > +-

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

Using or not using index

Running 4.1.22-standard, I have two simple MyISAM tables: Table: temp_del_ids Create Table: CREATE TABLE `temp_del_ids` ( `cust_id` int(11) NOT NULL default '0', PRIMARY KEY (`cust_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 and Table: cust_campaigns Create Table: CREATE TABLE `

Re: Why is simple query not using index?

On Tue, Mar 3, 2009 at 6:51 PM, Perrin Harkins 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 likel

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 wrote: > I'm using MySQL 5.0.67-

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 expir

BETWEEN, IN, >, < .... not using index with floats

Hi, im working with google maps and im and trying to do this, but i cant make a good query of it. I want to select elements between a given latitude and longitude from this table: *CREATE TABLE `images` (* *`id_img` bigint(20) unsigned NOT NULL auto_increment,** **`filename` char(50) NOT NULL,**

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.my

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 p

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.apca

Re: GROUP BY...not using index?

Also, if a significant number of your records have the 'active' attribute assigned to 1, hte query optimizer will see it as more efficient to do a full table scan rather go through all the indirection layers the index imposes. - michael On 9/13/07, Les Fletcher <[EMAIL PROTECTED]> wrote: > If I

Re: GROUP BY...not using index?

If I am not mistaken, group by only uses an index if the index is first used as part of the where clause and is compared to a constant. You'd need a two column index for this query: INDEX(active,food) The 'active' part would be used in the where clause, and the second part ( "food" ) could t

GROUP BY...not using index?

I have an index on `food` and on `active`, how come the result of the EXPLAIN doesn't show the query using an index? I'm concerned that as the query time will grow with the table. My Query: SELECT `food` , COUNT( `food` ) AS 'population' FROM `users` WHERE `active`=1 GROUP BY `food` LIMIT 0

Re: 4.1.16: updates not using index prefixes

Pete Harlan wrote: > FYI, > > 4.1.16 appears not to be using prefixes of compound indexes when doing > updates. Reverting to 4.1.15, or adding an index consisting of only > the desired field, restores reasonable behavior. > > I have added feedback to a possibly-related bug, >

4.1.16: updates not using index prefixes

FYI, 4.1.16 appears not to be using prefixes of compound indexes when doing updates. Reverting to 4.1.15, or adding an index consisting of only the desired field, restores reasonable behavior. I have added feedback to a possibly-related bug, , but wanted t

Query using join is not using index to sort the rows

Hi, I am having trouble trying to figure out the reason of this. The query (explained) is: EXPLAIN SELECT * FROM news,users WHERE news.user_id=users.user_id ORDER BY date DESC I divided the result table in two parts to improve readability: ++-+--+--+---

Re: Not using index?

Good Morning! Sasha Pachev wrote: Keith Thompson wrote: I've got a query that's not using an index and I don't understand why. Here's a stripped down version of what the tables look like: create table t1 ( id int(10) unsigned not null auto_increment, x1 date not null, ... 8 other small, fixed fi

Re: Not using index?

From: "Keith Thompson" <[EMAIL PROTECTED]> > select somefields from t1, t2 where t1.id = t2.id and t2.x2 = somenumber; Try: select somefields from t1 join t2 on t1.id = t2.id and t2.x2 = somenumber; Also take a look at the estimate number of records for each step in the explain output. It's

Re: Not using index?

Keith Thompson wrote: I've got a query that's not using an index and I don't understand why. Here's a stripped down version of what the tables look like: create table t1 ( id int(10) unsigned not null auto_increment, x1 date not null, ... 8 other small, fixed fields primary key (id),

Not using index?

I've got a query that's not using an index and I don't understand why. Here's a stripped down version of what the tables look like: create table t1 ( id int(10) unsigned not null auto_increment, x1 date not null, ... 8 other small, fixed fields primary key (id), key search_x1 (x1) )

Re: Why LIKE not using INDEX?

LIKE is a string comparison. In order to do it, MySQL has to convert the integer column into a string *before* it can test the string for a match. MySQL is not clever enough to know that a string consisting entirely of digits is compatible with an integer - as far as it is concerned "123%" is the

Why LIKE not using INDEX?

Hello I have a table with an intger column called ID. I have an index on it. Now I want to get a result with all the rows whos ID values start with lets say 12i.e. I want all the IDs with data - 12 123 1234 .. I am using this query select * from tablename where id like '123%'

RE: Why the query is not using index?

efore it can check if that value is less than 500. -Rob -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 3:51 PM To: [EMAIL PROTECTED] Subject: Why the query is not using index? Greetings Having a table with the following stru

Why the query is not using index?

--+-+ the KEY column is NULL i.e. it is not using Index? Why the query is not using index? Thanks in advance. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL Gen

MySQL not using Index?

This is probably indexed wrong, so I thought I would put it out to the list to see if it is.. or if I 'm just going to have to live with it. Anyway, I have two tables I'm joining together, and MySQL is not using the index on the column in the second table I'm joining on. Here is the explain and