MySQL Performance: MySQL => Two general MySQL questions..

Members

Help

Home

Home » Performance » MySQL » Two general MySQL questions..

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

Two general MySQL questions.. [message #522]

Tue, 02 January 2007 10:17

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

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

oordopjes
Messages: 15
Registered: January 2007

Junior Member

Hi (again

(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

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

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:

-=] Back to Top [=-

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

]

Current Time: Wed Jan 7 14:05:07 EST 2009

Total time taken to generate the page: 0.00778 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.