MySQL Forums
Forum List  »  General

Re: Reasons of some limitations
Posted by: Rick James
Date: May 24, 2016 09:32AM

Sometimes, finding a good workaround involves "thinking out of the box". Years ago, I would see people complain about the lack of subqueries in their version 4.0. Their query could easily be transformed into JOIN(s), but they were so used to subqueries in a competing product that they failed to think about JOIN. (As it turns out, MySQL's optimizer has been several years behind in development, so as recently as version 5.5 it was almost always better to use JOIN instead of subquery.)

I, too, have been irritated by not being able to use a TEMPORARY TABLE twice, and have yet to find a 'good' workaround. But I need it less than 1% of the time.

I don't think I have ever needed a FULL OUTER JOIN, and certainly not with more than two tables. Maybe I instinctively solve the problem with some workaround. What is your use case?

The MariaDB window functions seem to be reasonably clean and easy. Furthermore, the optimizer understands them and does a 'good' job (except in some extreme cases). Here are the docs:

https://mariadb.com/kb/en/mariadb/window-functions/

There was a talk at Percona Live in April, but the slides don't seem to be posted.

"Groupwise max" is a common task; it is trivially solved using windowing. There are many ways posted for solving it without windowing, but most are not efficient. I have tackled it here: http://mysql.rjweb.org/doc.php/groupwise_max

At the bottom of that blog are links to may of the tough scaling/performance problems I have encountered, together with solutions that work in MySQL. The one on PARTITIONing bluntly says there are only 4 use cases for which PARTITIONs improve performance. Newcomers to MySQL thing that PARTITIONs will solve their performance problems; it probably won't.

Options: ReplyQuote


Subject
Written By
Posted
Re: Reasons of some limitations
May 24, 2016 09:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.