2 ms·

I’m surprised this kind of optimization is not done by the query planner, seems like low hanging fruit. Maybe there are pathological cases that are more complex

• samlambert 4y ago
  > Maybe there are pathological cases that are more complex to solve? This. Building query planners is incredibly hard and doing too much magic can get really can trip up users.
• Sesse__ 4y ago
  It's not low-hanging fruit at all: * The gain depends on low-level details of your storage, can easily be negative, and is not necessarily easy to calculate precisely * It requires adding an extra join, which can be expensive (depending on a bazillion factors) and generally slows down your planner * The classic System R join optimization framework generally does not support adding or removing joins as part of cost-based optimization (although a pure Cascades-based optimizer probably would) * It's for something you shouldn't do in the first place (large OFFSET) Of course, the self-join is a hack. You could envision a cleaner approach where you separate out the index-only scan from a “fetch the real row” node, which can then be pulled up in certain cases (it does require you to track column sets, but isn't impossible), even past joins. However, see the last point. Also, MySQL's storage interface doesn't support it (you can push a WHERE clause down to be done before the full row fetch, and you can ask for the full row fetch to never be done at all, but you cannot ask for an explicit row fetch from an index lookup; that functionality is not exposed). It's fruit, but it definitely isn't low-hanging.