Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Two general MySQL questions..
Two general MySQL questions.. [message #522] Tue, 02 January 2007 10:17 Go to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
Hi,

1. When using SQL_CALC_FOUND_ROWS combined with a LIMIT and an ORDER BY. Does MySQL stop ordering after the LIMIT is reached? Or are all results ordered by MySQL while getting the SQL_CALC_FOUND_ROWS?
If this is the case it probably is faster to do a SELECT COUNT(*) instead..without an ORDER BY.

2. When MySQL (regular INNER??) JOINs two tables.. Does it first do a SELECT on table1 and a SELECT on table2 and then combine the results? Or does it do a SELECT on table1, join it.. and do another select?

Thanks!
Report message to a moderator
Re: Two general MySQL questions.. [message #525 is a reply to message #522 ] Tue, 02 January 2007 18:51 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
1) Good question. I'm not 100% sure but I would assume it does not, note even if you use LIMIT without SQL_CALC_FOUND_ROWS MySQL can only stop sorting on very last pass, so most work is performed anyway.

2) SELECT is SQL lever operation so it is neither, what happens it performs scan/lookup in the first table and for each row it performs lookup in the second table. EXPLAIN can be used to see what kind of lookup is used in both cases.

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: Two general MySQL questions.. [message #529 is a reply to message #525 ] Wed, 03 January 2007 18:23 Go to previous messageGo to next message
oordopjes is currently offline oordopjes
Messages: 15
Registered: January 2007
Junior Member
Hi (again Smile (as I have two treads with your answers)) Peter,

I really appreciate your help on this forum! Thanks for the answer and thanks for the forum itself!
Report message to a moderator
Re: Two general MySQL questions.. [message #553 is a reply to message #525 ] Tue, 09 January 2007 01:42 Go to previous messageGo to next message
rhuddleston is currently offline rhuddleston
Messages: 11
Registered: August 2006
Junior Member
> note even if you use LIMIT without SQL_CALC_FOUND_ROWS MySQL can only stop
> sorting on very last pass, so most work is performed anyway.

This brings up something I've been asking mysql to add for a while. If you have a query that has 5 sorts then a limit:

order by 1,2,3,4,5 limit 10;

MySQL should order column 1 then take the top 10 entries plus any additional entries that are the same as the 10th. Then the additional sorts could just sort on this smaller set. For the majority of cases this would hugely cut down on the speed of sorts.

I also noticed that having two sorts on a table can be over twice as slow as the single sort which is another reason to consider the limit when optimizing these.

-Ryan
Report message to a moderator
Re: Two general MySQL questions.. [message #555 is a reply to message #553 ] Tue, 09 January 2007 05:40 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Ryan,

You're right Sort with Limit can be done way better

There are also numerous other sort optimizations which can be implemented, for example

KEY(A)

ORDER BY A,B

One can still use index based sort and only reorder values for constant A

Order with Join also can be improved to use the index or only partial sort rather than filesort in many cases.

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:InnoDB tablespace inter-version compatibility
Next Topic:muti-key index order
Goto Forum:
  

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

Current Time: Sun Jul 5 17:13:02 EDT 2009

Total time taken to generate the page: 0.01251 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

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.