[MDEV-6568] Performance improvement: Order by column in a left joined table Created: 2014-08-13 Updated: 2015-11-17 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | None |
| Fix Version/s: | None |
| Type: | Task | Priority: | Minor |
| Reporter: | Jose Canciani | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | join, left, optimizer, order | ||
| Description |
|
Originally asked in https://answers.launchpad.net/maria/+question/252779 In a query like:
No index is used since b.c3 is a column of a left join table, so the optimizer ignores it. We have implemented an optimization in our client code that split the query in two: first part with WHERE b.c3 IS NULL, and a second part with WHERE b.c3 IS NOT NULL (depending on the order by ascending clause). It works great and most of the times the second part is not needed (when using limit). But it would be great if the database engine can figure it out by itself. |
| Comments |
| Comment by Sergei Golubchik [ 2014-08-22 ] |
|
Thanks for the optimization idea. As for now — the optimizer needs to know when to use this optimization. You suggest to split the join, it means MariaDB will do two joins, not one. This is slower. But, on the other hand, MariaDB won't need to do the filesort, and could retrieve in the index order. So, the optimizer needs to compare the cost of the additional join with the cost of the filesort. But, unfortunately, at the moment optimizer cannot properly estimate the cost of the filesort — this needs to be fixed first, before we could implement your suggestion. |