MySQL Performance: MySQL => How MySql optimises LIMIT 1 with ORDER BY by expression

Members

Help

Home

Home » Performance » MySQL » How MySql optimises LIMIT 1 with ORDER BY by expression

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

How MySql optimises LIMIT 1 with ORDER BY by expression [message #1721]

Mon, 03 September 2007 07:31

al.james
Messages: 16
Registered: October 2006

Junior Member

Hi there...

If I have a query that returns a single row (i.e. LIMIT 1) of a table ordered by an expression (I know order by expressions is sub-optimal, but its unavoidable).

I realise that a table scan is unavoidable, but what bothers me is that running EXPLAIN on my query says that mysql is using filesort.

Does this mean that it is sorting the entire table by this expression and then returns the first? This is crazy, clearly the lowest (or highest) scoring row could be pulled out during the table scan without an extra sorting. Indeed, this could apply to any LIMIT X query where X is quite small. You would just need to maintain a sorted list of those X...

Can someone confirm that mysql is indeed doing a full sort? Is there anyway to find out?

Report message to a moderator

Re: How MySql optimises LIMIT 1 with ORDER BY by expression [message #1722 is a reply to message #1721 ]

Mon, 03 September 2007 07:49

al.james
Messages: 16
Registered: October 2006

Junior Member

Here is an example of the SQL query:

SELECT item.item_id, log(n_votes)*score*((p0*-0.159223)+(p1*-0.424596)+(p2*-0.424596)+(p3*0.530745)+(p4*0.212298)+(p5*-0.106149)+(p6*0.424596)+(p7*0.212298)+(p8*-0.159223)+(p9*0.159223)) AS sim FROM item JOIN location USING(location_id) ORDER BY sim DESC LIMIT 1;

The values change per query, so its obvious that mysql will need to look at each row. Thats ok as long as its done efficiently. Is there anyway to calculate this with a index scan NOT a table scan? I tried creating an index across the fields p0-p9 but that did not help. If mysql was intelligent enough it could work out this query using an index scan (probably in memory)...

Report message to a moderator

Re: How MySql optimises LIMIT 1 with ORDER BY by expression [message #1723 is a reply to message #1721 ]

Mon, 03 September 2007 08:23

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Yes it does filesort.

Run explain and you'll see "filesort" which means it will be slow.

Generally sorting by expression is very bad idea - it is best to have it precomputed and stored in column. Which is though does not work for all applications.

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

Previous Topic:	Monitoring MySQL Queries
Next Topic:	Website statistics system on php/mysql

Goto Forum:

-=] Back to Top [=-

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

]

Current Time: Sun Jul 5 16:21:38 EDT 2009

Total time taken to generate the page: 0.00979 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.