mysql query optimization

Question

I would need some help on how to optimize the query.

select * from transaction where id < 7500001 order by id desc limit 16

when i do an explain plan on this - the type is "range" and rows is "7500000" According to the some online reference's this is explained as, it took the query 7,500,000 rows to scan and get the data.

Is there any way i can optimize so it uses less rows to scan and get the data. Also, id is the primary key column.

This should be a give, but just in case, I need to ask: is there an index on id? — Max Shawabkeh
– Max Shawabkeh, Commented May 7, 2010 at 2:51
@zerkms: I could swear that wasn't there when I read it, but there's no revision history. Weird. — Max Shawabkeh
– Max Shawabkeh, Commented May 7, 2010 at 3:01
@Max: it was definitely there, because it was there before I start answered. — zerkms
– zerkms, Commented May 7, 2010 at 3:21
yeah, as zerkms mentioned in his answer- why to retell what explain have said? Why not to bring the explain output itself? — Your Common Sense
– Your Common Sense, Commented May 7, 2010 at 4:09

zerkms · Accepted Answer · 2010-05-07 02:51:23Z

1

online reference's this is explained as, it took the query 7,500,000 rows to scan and get the data

not actually. it's the approximate (optimizer cannot say the correct number in many different cases) number of rows that potentially will be scanned. but you specified LIMIT - so only first 16 rows will be affected while query executed.

ps: i hope the used key in EXPLAIN is id?

answered May 7, 2010 at 2:51

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

newtover Over a year ago

The used key in EXPLAIN should be PRIMARY

zerkms Over a year ago

yep. actually it changes nothing. whether key (contains id as it leftmost part) is used - this query would be fast.

ceteras · Accepted Answer · 2010-05-07 07:06:41Z

0

I performed an explain with your query on a 8 million rows table

id   select_type  table       type  possible_keys  key      key_len  ref    rows     Extra
1   SIMPLE       transaction range PRIMARY        PRIMARY  8        NULL   4079100  Using where

The actual execution was fast, Execution Time : 00:00:00:044.

answered May 7, 2010 at 7:06

ceteras

3,3882 gold badges21 silver badges14 bronze badges

1 Comment

vamsivanka Over a year ago

Its not about time, but about the no. of rows it read to get the data. that's the only concern.

Collectives™ on Stack Overflow

mysql query optimization

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related