An insightful exploration by Domas (Facebook) on how some of the MySQL optimiser’s decision logic is sometimes naive, in this case regarding ORDER BY optimisation.
Quite often, “simple” logic can work better than complex logic as chasing all the corner cases can just make things worse – but sometimes, logic can be too simple.
Everything must be made as simple as possible, but no simpler.
– Albert Einstein / Roger Sessions
A useful overview of options, syntax and tools that have been deprecated or removed for the upcoming MySQL 5.7 release.
This is a little quiz (could be a discussion). I know what we tend to prefer (and why), but we’re interested in hearing additional and other opinions!
Given the way MySQL/MariaDB is architected, what would you prefer to see in a new server, more cores or higher clock speed? (presuming other factors such as CPU caches and memory access speed are identical).
For example, you might have a choice between
- 2x 2.4GHz 6 core, or
- 2x 3.0GHz 4 core
which option would you pick for a (dedicated) MySQL/MariaDB server, and why?
And, do you regard the “total speed” (N cores * GHz) as relevant in the decision process? If so, when and to what degree?
Open Query is supporting the mysql-cli Kickstarter project
(for MySQL and MariaDB) by Amjith Ramanujam who already successfully completed a similar tool for PostgreSQL.
It is a new MySQL client with Auto-Completion and Syntax Highlighting. From the info provided, it’s Python based, thus portable, and can be installed without root access. Could be a very useful tool. The good old mysql command line client does lack some things, yet a relatively low-level command line client is often useful for remote tasks (as opposed to graphical tools) so we reckon it’s good that this realm gets a bit of attention!
Maish Saidel-Keesing writes:
VMware announced last week the launch of VIO and there are a number of things that I think people are missing and should be pointed out.
Open Query Engineer Daniel Black and Engineer/Trainer Peter Lock will be presenting sessions at the upcoming Open Source Developers’ Conference
which is hosted at Griffith University Gold Coast Campus, 4-7 November 2014.
I also spotted
which should be very interesting as well. There many be more still, there are lots of sessions!
Full conference tickets cost less than $300 and include the lunches as well as the conference dinner, and all the tutorials/workshops in the main conference. Speaking from experience, OSDC is always great with good talks and excellent people to chat with.
With the conference over, the session videos are now online!
Friend and former colleague Roland Bouwman has written an excellent update on the GROUP BY implementation in MySQL
MySQL’s implementation of GROUP BY has historically been quirky. Sometimes that quirkiness has been useful, but often it causes grief as SQL authors can make mistakes that are executed but don’t produce the results they want (or expect).
SELECT cat, COUNT(val) as cnt, othercol FROM tbl GROUP BY cat
The ‘cat‘ column is in the GROUP BY clause, the COUNT(val) is an aggregate, but the ‘othercol‘ column is … well… neither. What used to effectively happen is that the server would pick one othercol value from within each group. As I noted before, sometimes useful but often a pest as the server wouldn’t know if you just made a mistake or whether you actually intended to use this ‘feature’.
The long existent sql_mode option ONLY_FULL_GROUP_BY mitigated some of that (but not fully in all cases – see Roland’s explanation).
With the sql_mode option enabled, the server chucks an error for faulty constructs, the most common one being:
Error: 1055 SQLSTATE: 42000 (ER_WRONG_FIELD_WITH_GROUP)
Message: ‘col’ isn’t in GROUP BY
MySQL 5.7.5 and above have ONLY_FULL_GROUP_BY enabled by default, with an updated implementation, so GROUP BY will now generally behave properly.
We know that many existing applications often rely on the previously “standard” but technically quirky/faulty behaviour. So when upgrading to this version or beyond, it’s important to
- use and test in development and staging environment first. This also means upgrading your dev environment first.
- scan the code for GROUP BY use, and ensure that the queries are correct.
- have proper error handling and reporting in your application.
- in some cases disable the sql_mode ONLY_FULL_GROUP_BY option, but that’s really a last resort. It’s best to fix the application.