MySQL Performance: MySQL => Performance Problem SELECT with FORCE INDEX

Members

Help

Home

Home » Performance » MySQL » Performance Problem SELECT with FORCE INDEX

Show: Today's Messages :: Show Polls :: Message Navigator

Performance Problem SELECT with FORCE INDEX [message #1117]

Thu, 19 April 2007 13:12

coller
Messages: 1
Registered: April 2007

Junior Member

I have the following performance problem with a select statement. The table is about 651.000 entries. The statement looks like:

SELECT SQL_CACHE ID, filename,
FROM table
FORCE INDEX ( ID_2 )
WHERE ID > 90000
ORDER BY page_rank DESC
LIMIT 245 , 15

The index ID_2 is:

Name:ID_2, Typ: INDEX, Cardinality: 651002, Fields: ID, page_rank

The request last very long, about 45 secs. The EXPLAIn shows the following:

table: table
type: range
possible_keys: ID_2
key: ID_2
Key_len:4
ref: NULL
rows: 651002
Extra: Using where; Using filesort

What do I wrong? Why does mysql run through the hole table? Why "Using filesort" even I use the index ID_2??

Please help! Thank in advance!

Report message to a moderator

Re: Performance Problem SELECT with FORCE INDEX [message #1118 is a reply to message #1117 ]

Fri, 20 April 2007 03:36

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

It's not running thru the whole table.

The "range" indicates that it performs a range scan of the index.

The "using filesort" has to do with your ORDER BY part of the query.

Some thoughts:
How many rows does the >90000 match?
Remember that all these rows has to be sorted due to your ORDER BY page_rank.

Create a combined index on (ID, page_rank) that way MySQL may use that to solve the sorting.

And as a last resort if you can't limit it better in the WHERE clause then above didn't help you, increase your sort_buffer_size because when that buffer is full mysql writes a temporary table to disk which slows down the query a lot.

Report message to a moderator

Re: Performance Problem SELECT with FORCE INDEX [message #1119 is a reply to message #1118 ]

Fri, 20 April 2007 18:05

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

You have index on (ID,page_rank) and ordering on page_rank

MySQL can't use index for order by in such case.

If ID > 90000 is not very selective simply index on page_rank will work better.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Performance Problem SELECT with FORCE INDEX [message #1128 is a reply to message #1119 ]

Sat, 21 April 2007 05:15

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

Peter wrote on Sat, 21 April 2007 00:05

You have index on (ID,page_rank) and ordering on page_rank

MySQL can't use index for order by in such case.
...

Right, sorry it can only use it when ID = const. Not ID < or > something.

Report message to a moderator

Previous Topic:	MySQL Performance Concerns
Next Topic:	Using transaction in C# lock time out problem

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 5 15:27:23 EDT 2009

Total time taken to generate the page: 0.00575 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.