[TYPO3-dev] Use of subqueries

[Christian Kuhn]

Hey,

Martin Kutschker wrote:
>> What exactly do you mean with EXPLAIN?
> 
> Mysql will tell you how it executes a query if you put an EXPLAIN before it.
> 
> Fire up phpMyAdmin and execute for example this statement:
> 
> EXPLAIN SELECT * FROM pages p LEFT JOIN pages_language_overlay plo ON (p.uid=plo.pid)
> 
> EXPLAIN helps a lot to check if your queries and indices are efficient! Mysql may surprise you how
> it uses your indices and in what order it executes the joins.

+1. EXPLAIN is really usefull.

A bit more about mysql performance:

It's important to know which queries are slow or could be improved.
my.cnf:
# Log slow queries and log queries not using indexes
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

Furthermore the FE admin panel is your friend. You could locate really 
slow extensions and look for long running queries. Take a special look 
to USER_INT queries. Always run queries on "real" data, if your tables 
are not big enough mysql might decide to not use any index at all.

More reading.
mysqlperformanceblog.com is very good, here are some of the gems you 
shouldn't miss:
http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/


Greetings
Christian