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

w *** id: 1 select_type: SIMPLE table: temp_del_ids type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 81 Extra: Using index *** 2. row

Re: Why is simple query not using index?

|SIMPLE|member|range|expiration|expiration|4||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 tabl

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

Re: log-queries-not-using-indexes and "Using index" in the Extra col of Explain

cardinality). > > -Original Message- > From: MySQLForum MySQLForum [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 29, 2008 6:35 AM > To: mysql@lists.mysql.com > Subject: log-queries-not-using-indexes and "Using index" in the Extra > col of Explain > > Hi Al

RE: log-queries-not-using-indexes and "Using index" in the Extra col of Explain

From: MySQLForum MySQLForum [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2008 6:35 AM To: mysql@lists.mysql.com Subject: log-queries-not-using-indexes and "Using index" in the Extra col of Explain Hi All, I have been using mysql 5.1.26-rc on Linux and configured to log queries that d

log-queries-not-using-indexes and "Using index" in the Extra col of Explain

| key_len | ref | rows | Extra | ++-+-+---+---+--+-+--+--+-+ | 1 | SIMPLE | table1 | index | NULL | (field1,field2,field3) | 107 | NULL | 2 | U

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: Using index for group-by: Not working?

It's just occurred to me that the IN clause is not a constant. This probably throws out any chance of using an index for group by? Cheers -Original Message- From: Andrew Armstrong [mailto:[EMAIL PROTECTED] Sent: Sunday, 29 July 2007 1:07 PM To: mysql@lists.mysql.com Subject:

RE: Using index for group-by: Not working?

e. -Original Message- From: Terry Mehlman [mailto:[EMAIL PROTECTED] Sent: Sunday, 29 July 2007 1:18 PM To: Andrew Armstrong Subject: Re: Using index for group-by: Not working? just a shot in the dark, but i would suggest two changes to your query. 1) put the count (distinct c5) first

Using index for group-by: Not working?

Hi, I have the following query: SELECT c2, c3, c4, Count(DISTINCT c5) FROM table1 WHERE c1 IN (1, 2, 3...) GROUP BY c2, c3, c4 order by null Yet I can only get it at best to show (under extra): Using where, using filesort. I have read up on: http://dev.mysql.com/doc/refman/5.0/

Using index returns an empty esult set

Hi I tried to create an index on one of my tables. After creating this index, the queries return an empty set, instead of the expected result. I have a table user looks like this: id - int(11) username - varchar(250) address - varchar(250) more columns... After creating an index:

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

Re: Optimal index for date range query with order by using index for sort???

Is the DATE field a timestamp column? What's your schema? What's your primary key? I ask this because the real question is, "is it safe to assume that new entries are for the current day it is inserted?" If the answer to that question is yes, you can use an id field (or an existing one) to find

Optimal index for date range query with order by using index for sort???

OK. I need help with the following query: SELECT * FROM PRODUCT WHERE DATE > ? ORDER BY PRICE; Basically find products created since a given date and order by prices. I could put an index of DATE, PRICE but it will have to resort to a filesort since DATE isn't a constant value. I was thin

Re: Slow Query Using Index

There is a limit, but that is really limited to the hardware you are running it on. You need to figure out what part of your system is bottlenecking (disk I/O, RAM, CPU, or network I/O). Perhaps you have to little RAM and/or your mysql configuration variables are not set optimally. Too litt

Slow Query Using Index

I have notices some slow queries showing up in my slow query log lately. Two of these queries are relatively simple queries using the index of their respective tables. Both of these tables are very large and I suspect this to be the problem. This problem appears to have started within the last

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?

t2 where t1.id = t2.id and t1.x1 = somedate This query runs painfully slow (usually several minutes): select somefields from t1, t2 where t1.id = t2.id and t2.x2 = somenumber; Using EXPLAIN, the first three queries above all report "using where; using index" for search_x1 or search_x2

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?

m t1, t2 where t1.id = t2.id and t1.x1 = somedate This query runs painfully slow (usually several minutes): select somefields from t1, t2 where t1.id = t2.id and t2.x2 = somenumber; Using EXPLAIN, the first three queries above all report "using where; using index" for search_x1 o

Not using index?

id = t2.id and t1.x1 = somedate This query runs painfully slow (usually several minutes): select somefields from t1, t2 where t1.id = t2.id and t2.x2 = somenumber; Using EXPLAIN, the first three queries above all report "using where; using index" for search_x1 or search_x2, wherea

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?

+ | 1 | SIMPLE | email_table | index | NULL | PRIMARY | 4 | N ULL | 33914 | Using where; Using index | ++-+-+---+---+-+-+-- +---+--+ This means it is using the index. But, if issue a

USING INDEX

ed; Using temporary | | u | ref | PRIMARY,CustomerID | CustomerID | 4 | c.CustomerID | 18 | || | ac| ref | UserID | U

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

Re: Query mych slower when using index (Innodb)

+ | table | type | possible_keys | key | key_len | ref | rows | Extra| +---++---+-+-+--++------+ | k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 | where use

Re: Query mych slower when using index (Innodb)

without the index on keywords.codice: mysql> show index from keywords; +--++--+--+-+---+-+--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |

Query mych slower when using index (Innodb)

| nsabbi> Extra| nsabbi> +---++---+-+-+--++--+ nsabbi> | k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 | nsabbi> where used; Using index; Using temporary |

Query mych slower when using index (Innodb)

| rows | Extra| +---++---+-+-+--++--+ | k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 | where used; Using index; Using temporary | | a | eq_ref | PRIMARY | PRIMARY | 16

Re: using index

On Thu, Aug 23, 2001 at 07:54:38PM +0200, Osus wrote: [snip] > when I run this query > mysql> explain select Nick from mytable where Inicial='P' AND Sexo=1 and > ok=1; > . > +--+--+---+-+--- > --+---+--++ > | table

using index

I have this table +---+--+--+-++-- --+ | Field | Type | Null | Key | Default| Extra | +---+--+--+-++-- --+ | ID| int(10) unsigned | | PRI