(initial placeholder... ticket will be enhanced incrementally, I apologize for initial missing informations)
apparently MariaDB 10.0.1, MariaDB 5.5.30, MySQL 5.5, 5.6 and Percona 5.5 are having the exact same behavior with a query that could be optimized, but I was expecting to run extremely quickly
It has correlated subqueries to populate some of the outputted columns,
but none of these are involved in the general WHERE clause
plus, these is also a LIMIT 18...
which brings down the whole thing to a very small dataset
I was expecting the query optimizer to apply first the WHERE clause,
than the LIMIT... then at last, execute the JOINS and the SUBQUERIES
Instead (in my case I have a 5mio row table, and the database/versions I mentioned above, are all computing first all the joins and all the subqueries, bringing everything in memory, and then as last thing applying the WHERE and the LIMIT... which is very... ehhrrr... nonsense ...IMHO)
I found a ticket, opened since 2007, on mysql.org, hitting exactly this scenario
I will provide a suitable test to be filled into this ticket (as all my tests are now based on internal tools,
and data dumps that can't be practically attached here), for now, I'm looking for comments...