Hot answers tagged

3

In the first query, only the customer_id needs to be saved from the customers into the hash table, because that is the only data needed to implement the semi-join. In the second query, all of the columns need to be stored into the hash table, because you are selecting all of the columns from the table (using *) rather than just testing for existence of the ...


3

The cause of the problem was identified by @Phil in the comments: Probably because it's nested too deep You have 2 layers of nesting and the reference of table e cannot "see" through these 2 layers in MySQL. Correlated inline subquery can usually be converted to derived tables and then LEFT joined to the other tables in the FROM clause but they have ...


3

All these nested derived tables (subselects inside subselects) are useless. You can easily remove all except 1 or 2. SELECT dev_hour.hour, rtdogs.delivery_date, rtdogs.delivery_hour, rtdogs.total_offer FROM delivery_hour AS dev_hour LEFT JOIN ( SELECT sum(greatest(ifnull(quantity1,0), ifnull(quantity2,0), ...


3

Then it looks like an optimizer's blind spot and you should use the second query. When there is a condition joining two tables a and b: a.id = b.id and an additional condition a.id > @some_constant, seems like the optimizer uses the "index condition" for where to start the index scan on a (id) index but it doesn't use it for the second index b (id). So, ...


2

The following pattern will often render very good performance, compared to self-joins and correlating subqueries: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY CustomerID, c1, c2, c3 ORDER BY OpenOrderDate DESC ) AS _rownum FROM some_table INNER JOIN ... INNER JOIN ... WHERE ... ) AS sub WHERE ...


1

Abandon all hope! First, there is a contradiction... You say FORCE INDEX(PRIMARY), yet PRIMARY is not an option; instead the better (actifVehicule) is being used. Please provide SHOW CREATE TABLE; it is difficult to guess at what indexes you have. Here's what is going on... It is starting with V0 and and filtering on actifVehicule=1. Since (I assume ...


1

You could need brackets. Try this: SELECT Actors.FirstName,Actors.LastName FROM ((Actors INNER JOIN ActorsMovies ON Actors.ActorID= ActorsMovies.ActorID) INNER JOIN GenresMovies ON ActorsMovies.MovieID=GenresMovies.MovieID);


1

Another way to rewrite the query, without having 2 correlated inline subqueries is to have instead one LEFT JOIN in the FROM clause. The problem is that MySQL does not have LATERAL joins so it looks a bit clumsy and is quite confusing to get it right: SELECT *, topics.createdate AS TopicCreateDate, lastpost_user.username AS LastPost_UserName, ...


1

When I've had to do this in the past one easy pattern I've followed is below: WITH LatestOpenOrderDate_CTE AS ( SELECT UserId , LatestOpenOrderDate = MAX(OpenOrderDate) FROM dbo.Foo GROUP BY UserId ) SELECT L.UserId , F.OpenOrderDate , F.Bar -- Just here to show how to get the other data from the matched row , B.Buzz -- Just here ...



Only top voted, non community-wiki answers of a minimum length are eligible