Re: Composite Index Usage in Joins

2012-07-11 Thread Sergei Petrunia 
On Tue, Jul 10, 2012 at 05:50:07PM -0400, Jeffrey Grollo wrote:
> Hi,
> 
> I’m attempting to optimize a join and am having a difficult time using
> multiple columns from a composite index. The second column of the composite
> key is being used when tested for equality, but not for IN or BETWEEN
> criteria.
> 
> As an example, say that I’m searching two tables: portfolio and trades. The
> portfolio table contains a list of security IDs. The trades table keeps
> tracks of the price and time when I’ve traded securities in my portfolio.
> Tables are:
> 
> CREATE TABLE portfolio (
>   sec_id bigint(20) NOT NULL AUTO_INCREMENT,
>   name char(10) NOT NULL,
>   PRIMARY KEY (sec_id)
> ) ENGINE=InnoDB ;
> 
> CREATE TABLE trades (
>   tx_id bigint(20) NOT NULL AUTO_INCREMENT,
>   sec_id bigint(20) NOT NULL,
>   trade_time datetime NOT NULL,
>   price int NOT NULL,
>   PRIMARY KEY (tx_id),
>   KEY sec_time (sec_id, trade_time)
> ) ENGINE=InnoDB ;
> 
> If I query the trades table directly both columns of the composite index
> "sec_time" will be used when I'm using a range criteria on the trade_time
> column:
> 
> 
> mysql> explain select price from trades force index(sec_time)
> -> where sec_id IN (1, 2) and trade_time BETWEEN '2012-07-01' AND
> '2012-07-04';
> ++-++---+---+--+-+--+--+-+
> | id | select_type | table  | type  | possible_keys | key  | key_len |
> ref  | rows | Extra   |
> ++-++---+---+--+-+--+--+-+
> |  1 | SIMPLE  | trades | range | sec_time  | sec_time | 16  |
> NULL |2 | Using where |
> ++-++---+---+--+-+--+--+-+
> 
> 
> If I introduce a join to retrieve all trades for my portfolio, the entire
> index will continue to be used if I make trade_time a constant:
> 
> mysql> explain select price from portfolio p inner join trades t force
> index(sec_time) on p.sec_id = t.sec_id
> -> where trade_time = '2012-07-01';
> ++-+---+---+---+--+-++--+-+
> | id | select_type | table | type  | possible_keys | key  | key_len |
> ref| rows | Extra   |
> ++-+---+---+---+--+-++--+-+
> |  1 | SIMPLE  | p | index | PRIMARY   | PRIMARY  | 8   |
> NULL   |1 | Using index |
> |  1 | SIMPLE  | t | ref   | sec_time  | sec_time | 16  |
> vantage.p.sec_id,const |1 | |
> ++-+---+---+---+--+-++--+-+
> 
> 
> However, if I expand the trade_time search (either using IN or BETWEEN),
> only the sec_id column of the composite query is used:
> 
> 
> mysql> explain select price from portfolio p inner join trades t force
> index(sec_time) on p.sec_id = t.sec_id
> -> where trade_time IN ('2012-07-01', '2012-07-02');
> ++-+---+---+---+--+-+--+--+-+
> | id | select_type | table | type  | possible_keys | key  | key_len |
> ref  | rows | Extra   |
> ++-+---+---+---+--+-+--+--+-+
> |  1 | SIMPLE  | p | index | PRIMARY   | PRIMARY  | 8   |
> NULL |1 | Using index |
> |  1 | SIMPLE  | t | ref   | sec_time  | sec_time | 8   |
> vantage.p.sec_id |1 | Using where |
> ++-+---+---+---+--+-+--+--+-+
> 
> 
> My expectation is that MySQL would be able to use both columns of the
> sec_time index, but I've been unable to find either confirmation of
> refutation of that assumption. 
I can provide a refutation. Ability to make a combined index access of

1. Equality with a non-constant: t.sec_id= p.sec_id 
2. non-equality comparison with constants, trade_time IN ('2012-07-01', 
'2012-07-02')

has been discussed a number of times by query optimizer developer, but as far 
as public knowlege goes, nobody has ever implemented it, either at Oracle, or 
at MariaDB, or elsewhere.

MariaDB 5.3+ and MySQL 5.6+ have Index Condition Pushdown feature which will
have the optimizer to check the condition

  trade_time IN ('2012-07-01', '2012-07-02')

before reading the fill tables.

> If MySQL cannot optimize a join in this
> case, is there another approach to optimizing this query that I should
> pursue? Essentially, my trades table may contain many historical records
> and pulling the entire history of trades for each security would produce a
> much larger result set than would be retrieved if the trade_time criteria
> was applied

Re: Composite Index Usage in Joins

2012-07-11 Thread Shawn Green 

On 7/10/2012 5:50 PM, Jeffrey Grollo wrote:

Hi,

I’m attempting to optimize a join and am having a difficult time using
multiple columns from a composite index. The second column of the composite
key is being used when tested for equality, but not for IN or BETWEEN
criteria.

As an example, say that I’m searching two tables: portfolio and trades. The
portfolio table contains a list of security IDs. The trades table keeps
tracks of the price and time when I’ve traded securities in my portfolio.
Tables are:

CREATE TABLE portfolio (
   sec_id bigint(20) NOT NULL AUTO_INCREMENT,
   name char(10) NOT NULL,
   PRIMARY KEY (sec_id)
) ENGINE=InnoDB ;

CREATE TABLE trades (
   tx_id bigint(20) NOT NULL AUTO_INCREMENT,
   sec_id bigint(20) NOT NULL,
   trade_time datetime NOT NULL,
   price int NOT NULL,
   PRIMARY KEY (tx_id),
   KEY sec_time (sec_id, trade_time)
) ENGINE=InnoDB ;

If I query the trades table directly both columns of the composite index
"sec_time" will be used when I'm using a range criteria on the trade_time
column:


mysql> explain select price from trades force index(sec_time)
 -> where sec_id IN (1, 2) and trade_time BETWEEN '2012-07-01' AND
'2012-07-04';
++-++---+---+--+-+--+--+-+
| id | select_type | table  | type  | possible_keys | key  | key_len |
ref  | rows | Extra   |
++-++---+---+--+-+--+--+-+
|  1 | SIMPLE  | trades | range | sec_time  | sec_time | 16  |
NULL |2 | Using where |
++-++---+---+--+-+--+--+-+


If I introduce a join to retrieve all trades for my portfolio, the entire
index will continue to be used if I make trade_time a constant:

mysql> explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
 -> where trade_time = '2012-07-01';
++-+---+---+---+--+-++--+-+
| id | select_type | table | type  | possible_keys | key  | key_len |
ref| rows | Extra   |
++-+---+---+---+--+-++--+-+
|  1 | SIMPLE  | p | index | PRIMARY   | PRIMARY  | 8   |
NULL   |1 | Using index |
|  1 | SIMPLE  | t | ref   | sec_time  | sec_time | 16  |
vantage.p.sec_id,const |1 | |
++-+---+---+---+--+-++--+-+


However, if I expand the trade_time search (either using IN or BETWEEN),
only the sec_id column of the composite query is used:


mysql> explain select price from portfolio p inner join trades t force
index(sec_time) on p.sec_id = t.sec_id
 -> where trade_time IN ('2012-07-01', '2012-07-02');
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type  | possible_keys | key  | key_len |
ref  | rows | Extra   |
++-+---+---+---+--+-+--+--+-+
|  1 | SIMPLE  | p | index | PRIMARY   | PRIMARY  | 8   |
NULL |1 | Using index |
|  1 | SIMPLE  | t | ref   | sec_time  | sec_time | 8   |
vantage.p.sec_id |1 | Using where |
++-+---+---+---+--+-+--+--+-+


My expectation is that MySQL would be able to use both columns of the
sec_time index, but I've been unable to find either confirmation of
refutation of that assumption. If MySQL cannot optimize a join in this
case, is there another approach to optimizing this query that I should
pursue? Essentially, my trades table may contain many historical records
and pulling the entire history of trades for each security would produce a
much larger result set than would be retrieved if the trade_time criteria
was applied in the index reference.

I'm using MySQL 5.5.11.

Thanks for any guidance,
Jeff



Indexes are stored as b-TREE structures. For InnoDB tables, the leaf 
nodes of the tree will either contain the PRIMARY KEY of the table or a 
6-byte hidden value that acts as a row identifier.


The tree is structured so that the key values are parsed together and a 
balanced binary tree is built that represents the range of values on the 
table. For example, the index entry for one of your rows could be 
"1_2012-07-01". At each level above the leaves, you have a node that 
lists the first and last elements of the range of leaves beneath it. For 
example an intermediate node may have "1_2009-01-01" and "1_2009-10-15" 
to represent that those are the values within that branch of the tree

Re: why does "select * from table oder by indexed_field" not use key?

2012-07-11 Thread Stephen Tu 
On Wed, Jul 11, 2012 at 5:46 AM, Reindl Harald wrote:

> > mysql> show profiles;
> >
> +--++--+
> > | Query_ID | Duration   | Query
> > |
> >
> +--++--+
> > |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
> > cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
> > |2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
> > cms1_quickbar_groups ORDER BY qg_sort ASC |
> >
> +--++--+
> > 2 rows in set (0.00 sec)
>
> this may be true for small data where it does not matter at all
> but if this would be a large table it would cause a lot of I/O
>
>
While I agree with you that Ewen's microbenchmark is not the most
convincing, I do agree with his reasoning, and I encourage you to try
benchmarking both options on your dataset. Remember to flush both the OS
disk cache and the mysql buffer pool between runs, so that your benchmarks
are actually reflecting cold runs instead of partially warmed up runs.

So why do I believe no index is faster for your particular query? Well, a
secondary index (qbq_key in your case) is usually key/value pairs of the
form [index key, page ID pointing to tuple]. So if we answered your query
with qbq_key, we don't need to do a sort, *but* we'll need to do roughly
one disk seek for each key in the index (I'm assuming here that qg_sort
values don't have any strong correlation with qg_id, and that the pages for
the table aren't already in the buffer pool/OS disk cache).

Compare that with a case where we don't use an index, so we must do a
filesort. Since mysql has an optimization (
https://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html - read
the "modified filesort algorithm" section) which stores the entire tuple
(if under a threshold size controlled by max_length_for_sort_data, which is
1024 bytes by default which your schema seems to fall under) when sorting
instead of just the [sort key, page ID], the filesort can actually avoid
much of random disk seeks (since mergesort itself is a very sequential IO
heavy algorithm). So for this plan, you end up basically doing several
sequential IOs over the entire table (whereas the previous plan just had to
read the entire table once, albeit randomly). Most likely the mysql
optimizer has calculated that several sequential scans over the table are
much faster than a bunch of random disk seeks which reads the table at
once. In fact, I believe the general rule of thumb for DB optimizers is
that if you need to read more than 10% of a table from an index, you are
better off using a sequential scan.

Like I said, I encourage you to measure the performance to convince
yourself that mysql is actually doing the right thing.

Re: MySQL crashed..

2012-07-11 Thread J M 
sorry.. im using 5.1

On Wed, Jul 11, 2012 at 10:09 AM, J M  wrote:
> hi all,
>
>   our DB crashed for some reason... any inputs would be greatly appreciated..
>
> 120711  8:12:21 - mysqld got signal 11 ;
> This could be because you hit a bug. It is also possible that this binary
> or one of the libraries it was linked against is corrupt, improperly built,
> or misconfigured. This error can also be caused by malfunctioning hardware.
> We will try our best to scrape up some info that will hopefully help diagnose
> the problem, but since we have already crashed, something is definitely wrong
> and this may fail.
>
> key_buffer_size=268435456
> read_buffer_size=262144
> max_used_connections=148
> max_threads=600
> threads_connected=35
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1650750 
> K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> Thread pointer: 0x2aaafc19ddb0
> Attempting backtrace. You can use the following information to find out
> where mysqld died. If you see no messages after this, something went
> terribly wrong...
> stack_bottom = 0x43705f08 thread_stack 0x4
> /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x85836e]
> /usr/sbin/mysqld(handle_segfault+0x34f)[0x5c7c6f]
> /lib64/libpthread.so.0[0x3486c0eb10]
> /lib64/libc.so.6[0x348607051b]
> /lib64/libc.so.6[0x3486072bbc]
> /lib64/libc.so.6(__libc_malloc+0x6e)[0x3486074e2e]
> /usr/sbin/mysqld(my_malloc+0x32)[0x847842]
> /usr/sbin/mysqld(_ZN6String10real_allocEj+0x35)[0x5c1535]
> /usr/sbin/mysqld(handle_one_connection+0x687)[0x5cc4a7]
> /lib64/libpthread.so.0[0x3486c0673d]
> /lib64/libc.so.6(clone+0x6d)[0x34860d40cd]
>
> Trying to get some variables.
> Some pointers may be invalid and cause the dump to abort.
> Query ((nil)): is an invalid pointer
> Connection ID (thread ID): 8415116
> Status: NOT_KILLED
>
> The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
> information that should help you find out what is causing the crash.
> 120711  8:12:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table
> './mydb/wp_posts.MYI'; try to repair it
> 120711  8:12:22 [ERROR] Got error 126 when reading table './mydb/wp_posts'
>
> ** OS error code 126:  Required key not available
>
> Im using MySQL 5.0
>
> tia,

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

MySQL crashed..

2012-07-11 Thread J M 
hi all,

  our DB crashed for some reason... any inputs would be greatly appreciated..

120711  8:12:21 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=262144
max_used_connections=148
max_threads=600
threads_connected=35
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1650750 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2aaafc19ddb0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x43705f08 thread_stack 0x4
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x85836e]
/usr/sbin/mysqld(handle_segfault+0x34f)[0x5c7c6f]
/lib64/libpthread.so.0[0x3486c0eb10]
/lib64/libc.so.6[0x348607051b]
/lib64/libc.so.6[0x3486072bbc]
/lib64/libc.so.6(__libc_malloc+0x6e)[0x3486074e2e]
/usr/sbin/mysqld(my_malloc+0x32)[0x847842]
/usr/sbin/mysqld(_ZN6String10real_allocEj+0x35)[0x5c1535]
/usr/sbin/mysqld(handle_one_connection+0x687)[0x5cc4a7]
/lib64/libpthread.so.0[0x3486c0673d]
/lib64/libc.so.6(clone+0x6d)[0x34860d40cd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query ((nil)): is an invalid pointer
Connection ID (thread ID): 8415116
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
120711  8:12:22 [ERROR] /usr/sbin/mysqld: Incorrect key file for table
'./mydb/wp_posts.MYI'; try to repair it
120711  8:12:22 [ERROR] Got error 126 when reading table './mydb/wp_posts'

** OS error code 126:  Required key not available

Im using MySQL 5.0

tia,

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: why does "select * from table oder by indexed_field" not use key?

2012-07-11 Thread Ananda Kumar 
column used in the order by caluse, should be the first column in the
select statement to make the index work

On Wed, Jul 11, 2012 at 3:16 PM, Reindl Harald wrote:

>
>
> Am 11.07.2012 11:43, schrieb Ewen Fortune:
> > Hi,
> >
> > On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald 
> wrote:
> >> the mysql query optimizer is somehow stupid
> >
> > Its not stupid - remember its not trying to find the best index,
> > rather its trying to find the least costly plan
> > to return the data in the quickest manner.
> >
> > For the optimizer in this case it believes its faster to do a full
> > table scan with filesort rather than read from the index
> > and have to scan the entire table anyway.
> >
> > Quick test shows it is indeed faster to do a full table scan.
> >
> > mysql> show profiles;
> >
> +--++--+
> > | Query_ID | Duration   | Query
> > |
> >
> +--++--+
> > |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
> > cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
> > |2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
> > cms1_quickbar_groups ORDER BY qg_sort ASC |
> >
> +--++--+
> > 2 rows in set (0.00 sec)
>
> this may be true for small data where it does not matter at all
> but if this would be a large table it would cause a lot of I/O
>
>

Re: why does "select * from table oder by indexed_field" not use key?

2012-07-11 Thread Reindl Harald 


Am 11.07.2012 11:43, schrieb Ewen Fortune:
> Hi,
> 
> On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald  
> wrote:
>> the mysql query optimizer is somehow stupid
> 
> Its not stupid - remember its not trying to find the best index,
> rather its trying to find the least costly plan
> to return the data in the quickest manner.
> 
> For the optimizer in this case it believes its faster to do a full
> table scan with filesort rather than read from the index
> and have to scan the entire table anyway.
> 
> Quick test shows it is indeed faster to do a full table scan.
> 
> mysql> show profiles;
> +--++--+
> | Query_ID | Duration   | Query
> |
> +--++--+
> |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
> cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
> |2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
> cms1_quickbar_groups ORDER BY qg_sort ASC |
> +--++--+
> 2 rows in set (0.00 sec)

this may be true for small data where it does not matter at all
but if this would be a large table it would cause a lot of I/O



signature.asc
Description: OpenPGP digital signature

Re: why does "select * from table oder by indexed_field" not use key?

2012-07-11 Thread Ewen Fortune 
Hi,

On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald  wrote:
> the mysql query optimizer is somehow stupid

Its not stupid - remember its not trying to find the best index,
rather its trying to find the least costly plan
to return the data in the quickest manner.

For the optimizer in this case it believes its faster to do a full
table scan with filesort rather than read from the index
and have to scan the entire table anyway.

Quick test shows it is indeed faster to do a full table scan.

mysql> show profiles;
+--++--+
| Query_ID | Duration   | Query
|
+--++--+
|1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
|2 | 0.24592100 | SELECT SQL_NO_CACHE * FROM
cms1_quickbar_groups ORDER BY qg_sort ASC |
+--++--+
2 rows in set (0.00 sec)


Cheers,

Ewen

>
> a simple query, order by with a indexed column and
> you have to use where order_by_field>0 - why the
> hell is mysqld not happy that a key is on the field
> used in "order by"?
>
> mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
> ++-+--+--+---+--+-+--+--++
> | id | select_type | table| type | possible_keys | key  | 
> key_len | ref  | rows | Extra  |
> ++-+--+--+---+--+-+--+--++
> |  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL | 
> NULL| NULL |2 | Using filesort |
> ++-+--+--+---+--+-+--+--++
> 1 row in set (0.00 sec)
>
> mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups where qg_sort>0 ORDER BY 
> qg_sort ASC;
> ++-+--+---+---+-+-+--+--+-+
> | id | select_type | table| type  | possible_keys | key | 
> key_len | ref  | rows | Extra   |
> ++-+--+---+---+-+-+--+--+-+
> |  1 | SIMPLE  | cms1_quickbar_groups | range | qbq_key   | qbq_key | 
> 2   | NULL |2 | Using where |
> ++-+--+---+---+-+-+--+--+-+
> 1 row in set (0.00 sec)
>
>
> Am 11.07.2012 02:39, schrieb Akshay Suryavanshi:
>> The statement will do a Full table scan, because of the following things : 
>> Not using "Where" clause, and selecting
>> "all columns (*)" within the query. Filesort is used since no index is used, 
>> use a where clause with condition on
>> column which is indexed and notice the explain plan. Also you can retrieve 
>> specific columns on which indexes are
>> created to use the feature of "Covering index".
>>
>> On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald > > wrote:
>>
>> my reason for create a key on "qg_sort" was primary
>> for this query - but why is here 'filesort' used?
>>
>> mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
>> 
>> ++-+--+--+---+--+-+--+--++
>> | id | select_type | table| type | possible_keys | key  
>> | key_len | ref  | rows | Extra  |
>> 
>> ++-+--+--+---+--+-+--+--++
>> |  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL 
>> | NULL| NULL |2 | Using filesort |
>> 
>> ++-+--+--+---+--+-+--+--++
>> 1 row in set (0.01 sec)
>> -
>> cms1_quickbar_groups | CREATE TABLE `cms1_quickbar_groups` (
>>   `qg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
>>   `qg_titel` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
>>   `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT 
>> '',
>>   `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0',
>>   PRIMARY KEY (`qg_id`),
>>   KEY `qbq_key` (`qg_sort`)
>> ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 
>> COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1
>>
>>
>
> --
>
> Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / CISO / Software-Development
> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
> icq: 154546

Re: why does "select * from table oder by indexed_field" not use key?

2012-07-11 Thread Reindl Harald 
the mysql query optimizer is somehow stupid

a simple query, order by with a indexed column and
you have to use where order_by_field>0 - why the
hell is mysqld not happy that a key is on the field
used in "order by"?

mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
++-+--+--+---+--+-+--+--++
| id | select_type | table| type | possible_keys | key  | 
key_len | ref  | rows | Extra  |
++-+--+--+---+--+-+--+--++
|  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL | NULL  
  | NULL |2 | Using filesort |
++-+--+--+---+--+-+--+--++
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups where qg_sort>0 ORDER BY 
qg_sort ASC;
++-+--+---+---+-+-+--+--+-+
| id | select_type | table| type  | possible_keys | key | 
key_len | ref  | rows | Extra   |
++-+--+---+---+-+-+--+--+-+
|  1 | SIMPLE  | cms1_quickbar_groups | range | qbq_key   | qbq_key | 2 
  | NULL |2 | Using where |
++-+--+---+---+-+-+--+--+-+
1 row in set (0.00 sec)


Am 11.07.2012 02:39, schrieb Akshay Suryavanshi:
> The statement will do a Full table scan, because of the following things : 
> Not using "Where" clause, and selecting
> "all columns (*)" within the query. Filesort is used since no index is used, 
> use a where clause with condition on
> column which is indexed and notice the explain plan. Also you can retrieve 
> specific columns on which indexes are
> created to use the feature of "Covering index".
> 
> On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald  > wrote:
> 
> my reason for create a key on "qg_sort" was primary
> for this query - but why is here 'filesort' used?
> 
> mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
> 
> ++-+--+--+---+--+-+--+--++
> | id | select_type | table| type | possible_keys | key  | 
> key_len | ref  | rows | Extra  |
> 
> ++-+--+--+---+--+-+--+--++
> |  1 | SIMPLE  | cms1_quickbar_groups | ALL  | NULL  | NULL | 
> NULL| NULL |2 | Using filesort |
> 
> ++-+--+--+---+--+-+--+--++
> 1 row in set (0.01 sec)
> -
> cms1_quickbar_groups | CREATE TABLE `cms1_quickbar_groups` (
>   `qg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
>   `qg_titel` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
>   `qg_titel_en` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT 
> '',
>   `qg_sort` smallint(3) unsigned NOT NULL DEFAULT '0',
>   PRIMARY KEY (`qg_id`),
>   KEY `qbq_key` (`qg_sort`)
> ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 
> COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1
> 
> 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm





signature.asc
Description: OpenPGP digital signature