There are occasions when mysql drives me mad!
A classic example is selecting from one table and joining with another table using one or more fields which are unique keys in the second table. In this case first table is some service, like a VoIP number, and second table is the login details relating to the login field in the service. e.g.
SELECT * FROM Service LEFT JOIN Login USING (login) WHERE blah;
Well, that generally works when blah is simple, but if the WHERE is even slightly complicated (referencing Login and Service), even if only looking at fields that have an index, it can break badly inspecting every combination of Service and Login!
The other approach I tried looks much worse. The SQL does not know using a nice syntax like "USING" what I am up to and is having to guess. e.g.
SELECT * FROM Service,Login WHERE Service.login=Login.login AND (blah).
Yet, for some inexplicable reason, MYSQL really likes the second way of doing it. And is lightningly quick. The first way was still going 10 minutes later when I restarted the server.
What is sofa king annoying is that this used to be the other way around in terms of efficiency in a previous release of mysql. Long ago we changed from the WHERE type joining to using USING to speed it up. Now we have to change back.
Arrrg!
Subscribe to:
Post Comments (Atom)
Fencing
Bit of fun... We usually put up some Christmas lights on the house - some fairy lights on the metal fencing at the front, but a pain as mean...
-
Broadband services are a wonderful innovation of our time, using multiple frequency bands (hence the name) to carry signals over wires (us... -
For many years I used a small stand-alone air-conditioning unit in my study (the box room in the house) and I even had a hole in the wall fo... -
It seems there is something of a standard test string for anti virus ( wikipedia has more on this). The idea is that systems that look fo...
postgresql is really quite nice...
ReplyDeleteIf you've been using MySQL for years, suddenly changing to administrate a postgresql server is anything but nice.
ReplyDeleteI've never understood how the worst of the open source databases ended up being the one everyone used. Before InnoDB I remember MySQL fanboys trying to tell me that transactions and WAL weren't important. Now MySQL supports them, so the fanboys say they are important, and MySQL is a real database because it supports these essential features.
ReplyDeleteBTW, does the bad execution plan arise from the use of an outer join, or the use of the 'join' keyword? In other words, if you take the 'left' keyword out of the first query, does it speed up? (I can't remember the exact syntax for an inner join in MySQL, but I'm sure you get the idea.)
Good question. Technically we want a left join as I want to find Service where there is no corresponding Login if that matches, and the new way does not find that. However, such cases are an error and can be found by separate (simpler) queries.
ReplyDeleteI though that USING is syntactic sugar for ON which in turn is syntactic sugar for a clause ANDed with the WHERE condition.
ReplyDeleteYour only difference should the join type, LEFT vs INNER.
Though TBH I can't believe that you are still using MySQL. It just doesn't fit your "I like to do things right".
"I want to find Service where there is no corresponding Login if that matches, and the new way does not find that. However, such cases are an error and can be found by separate (simpler) queries"
Presumably there is some killer reason why you do not use a proper database where you could specify a foreign key constraint? With that, there could simply never be no corresponding login record. Then you could always use an inner join. Inner joins are of course symmetric so giving the query planner more scope for optimisation.
Yours,
A fellow pedant
I appreciate the comments on a "proper database" but mysql is what we know and love(!).
ReplyDeleteJudging by the title of this article, it seems it might be better to say "know, and love to hate" :-)
ReplyDeleteIf you ever fancy having a go at PostgreSQL, feel free to use me as a telephone hotline --- whilst it is a superb system with copious quality documentation, for people who are coming out of (escaping from?) MySQL there are a couple of head-scratch issues wherein a person with the relevant experience could save you a lot of time with very little of theirs.