Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1653

LP #1500639: Optimizer chooses wrong index for ORDER BY DESC

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Sveta Smirnova last update 29-09-2015 05:36:20

      When querying a table on two fields, one of which has much more rows which satisfy condition than another which also has partial index on it, index used for the former (with more rows).

      E.g. for general case WHERE f1=X AND f2=Y ORDER BY f2 DESC LIMIT 1;
      f1 has 64 rows, equal to X and f2 has 320 rows, equal to Y, optimizer chooses index on f2 and not partial index on f1 which gives better results.

      This can lead to high query execution times, especially in cases when table has thousands of rows which satisfy condition f2=Y and no rows, which satisfy condition f1=X

      File with test case will be attached shortly.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  lpjirasync lpjirasync (Inactive)
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated: