Mysql choosing really poor index without IGNORE INDEX hint



  • Problem

    The following query produces a really inefficient query plan:

    select `event`.`id` as `eventId` 
    from `alert`
    inner join `event` on `event`.`id` = `alert`.`eventId` 
    inner join `property` on `property`.`id` = `event`.`propertyId`
    inner join `landlord` on `landlord`.`id` = `property`.`landlordId`
    where `landlord`.`reference` in ( 
         'landlord1',
         'landlord2',
         'landlord3',
         'landlord4',
         'landlord5',
         'landlord6',
         'landlord7',   
         'landlord8'
    )
    order by `alert`.`raiseddate` desc limit 20;
    

    Query plan:


    The index of note here is alert_eventIdRaisedDate on Alert(`eventId`,`raisedDate` DESC)

    Timing: 4008ms

    By forcing mysql to ignore a specific index, things are much much faster.

    select `event`.`id` as `eventId` 
    from `alert` ignore index (alert_eventIdRaisedDate)
    inner join `event` on `event`.`id` = `alert`.`eventId` 
    inner join `property` on `property`.`id` = `event`.`propertyId`
    inner join `landlord` on `landlord`.`id` = `property`.`landlordId`
    where `landlord`.`reference` in ( 
         'landlord1',
         'landlord2',
         'landlord3',
         'landlord4',
         'landlord5',
         'landlord6',
         'landlord7',   
         'landlord8'
    )
    order by `alert`.`raiseddate` desc limit 20;
    

    Query plan:

    Timing: 83ms

    If we execute the query without a landlord where clause, no hint is required and it's really fast.

    select `event`.`id` as `eventId` 
    from `alert` 
    inner join `event` on `event`.`id` = `alert`.`eventId` 
    inner join `property` on `property`.`id` = `event`.`propertyId`
    inner join `landlord` on `landlord`.`id` = `property`.`landlordId`
    order by `alert`.`raiseddate` desc limit 20;
    

    Query Plan:

    Timing: 18ms

    Thoughts

    I believe the performance penalty without the index hint is incurred due to using temporary, using file sort which means mysql is not able to satisfy the order by clause from an index. alert_eventIdRaisedDate on Alert(`eventId`,`raisedDate` DESC), so why is my sql choosing this index and what can be done to either the query, schema or indexes to remove the need for the ignore index hint?

    I have tested this on both mysql 5.7 and mysql 8, same same. Details captured in this question are from mysql 8.0

    Schema

    CREATE TABLE `alert` (
      `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      `title` varchar(255) NOT NULL,
      `description` varchar(255) NOT NULL,
      `value` decimal(10,4) DEFAULT NULL,
      `valueUnitId` varchar(255) DEFAULT NULL,
      `eventId` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      `test` varchar(255) DEFAULT NULL,
      `typeId` varchar(255) NOT NULL,
      `statusId` varchar(255) NOT NULL,
      `raisedDate` datetime(3) NOT NULL,
      `resolvedDate` datetime(3) DEFAULT NULL,
      `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      `snoozedUntil` datetime(3) DEFAULT NULL,
      `autoCancel` tinyint(1) NOT NULL DEFAULT '0',
      `resolvingEventId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `eventId_UNIQUE` (`eventId`) /*!80000 INVISIBLE */,
      UNIQUE KEY `alert_EventIdTypeIdStatusIdUnique` (`eventId`,`typeId`,`statusId`) /*!80000 INVISIBLE */,
      KEY `valueUnitId` (`valueUnitId`),
      KEY `test` (`test`),
      KEY `typeId` (`typeId`),
      KEY `alert_resolvingEventId_foreign_idx` (`resolvingEventId`),
      KEY `idx_alert_raisedDateEventIdStatusId` (`raisedDate`,`statusId`,`eventId`),
      KEY `alert_raisedDate` (`raisedDate`),
      KEY `alert_eventIdStatusId` (`eventId`,`statusId`) /*!80000 INVISIBLE */,
      KEY `alert_idEventIdStatusId` (`id`,`eventId`,`statusId`),
      KEY `alert_eventIdId` (`eventId`,`id`) /*!80000 INVISIBLE */,
      KEY `alert_idEventId` (`id`,`eventId`),
      KEY `alert_statusIdTypeIdEventId` (`statusId`,`typeId`,`eventId`),
      KEY `alert_EventIdTypeIdStatusId` (`eventId`,`typeId`,`statusId`) /*!80000 INVISIBLE */,
      KEY `alert_eventIdRaisedDate` (`eventId`,`raisedDate` DESC),
      KEY `alert_raisedDateEventId` (`raisedDate` DESC,`eventId`),
      CONSTRAINT `alert_ibfk_1` FOREIGN KEY (`valueUnitId`) REFERENCES `measurementunit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `alert_ibfk_2` FOREIGN KEY (`eventId`) REFERENCES `event` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `alert_ibfk_3` FOREIGN KEY (`test`) REFERENCES `measurementunit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `alert_ibfk_4` FOREIGN KEY (`typeId`) REFERENCES `alerttype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `alert_ibfk_5` FOREIGN KEY (`statusId`) REFERENCES `alertstatus` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `alert_resolvingEventId_foreign_idx` FOREIGN KEY (`resolvingEventId`) REFERENCES `event` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    

    and

    CREATE TABLE `event` (
      `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      `name` varchar(255) NOT NULL,
      `description` varchar(255) NOT NULL,
      `active` tinyint(1) NOT NULL DEFAULT '1',
      `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      `eventTypeId` varchar(255) NOT NULL,
      `sourceId` varchar(255) NOT NULL,
      `deviceId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
      `propertyId` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      `raisedDate` datetime(3) NOT NULL,
      `insightId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
      `raisedYear` int GENERATED ALWAYS AS (year(`raisedDate`)) STORED,
      `raisedMonth` int GENERATED ALWAYS AS (month(`raisedDate`)) STORED,
      `raisedDay` int GENERATED ALWAYS AS (dayofmonth(`raisedDate`)) STORED,
      PRIMARY KEY (`id`),
      UNIQUE KEY `event_UNIQUE` (`deviceId`,`insightId`,`propertyId`,`raisedDate`,`eventTypeId`) /*!80000 INVISIBLE */,
      UNIQUE KEY `event_DEVICE` (`deviceId`,`raisedDate`,`eventTypeId`) /*!80000 INVISIBLE */,
      UNIQUE KEY `event_INSIGHT` (`insightId`,`raisedDate`,`eventTypeId`) /*!80000 INVISIBLE */,
      KEY `sourceId` (`sourceId`) /*!80000 INVISIBLE */,
      KEY `idx_eventtypeId_id` (`eventTypeId`,`id`) /*!80000 INVISIBLE */,
      KEY `idx_eventType_deviceId_raisedDate` (`eventTypeId`,`deviceId`,`raisedDate`) /*!80000 INVISIBLE */,
      KEY `event_propertyIdRaisedDate` (`propertyId`,`raisedDate`),
      KEY `event_raiseDate` (`raisedDate`),
      KEY `event_eventTypeIdraiseDate` (`eventTypeId`,`raisedDate`) /*!80000 INVISIBLE */,
      KEY `event_eventTypeIdPropertyidraiseDate` (`eventTypeId`,`propertyId`,`raisedDate`),
      KEY `event_raisedDatePropertyId` (`raisedDate`,`propertyId`) /*!80000 INVISIBLE */,
      KEY `event_raisedDateDESC` (`raisedDate` DESC),
      KEY `event_propertyIdRaisedDateDESC` (`propertyId`,`raisedDate` DESC),
      CONSTRAINT `event_ibfk_1` FOREIGN KEY (`eventTypeId`) REFERENCES `eventtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `event_ibfk_2` FOREIGN KEY (`sourceId`) REFERENCES `source` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `event_ibfk_3` FOREIGN KEY (`deviceId`) REFERENCES `device` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `event_ibfk_4` FOREIGN KEY (`propertyId`) REFERENCES `property` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `event_insightId_foreign_idx` FOREIGN KEY (`insightId`) REFERENCES `insight` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    

    and

    CREATE TABLE `property` (
      `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      `name` varchar(255) NOT NULL,
      `reference` varchar(255) NOT NULL,
      `parentId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
      `landlordId` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      `addressId` int NOT NULL,
      `geoId` int NOT NULL,
      `typeId` varchar(255) NOT NULL,
      `statusId` varchar(255) NOT NULL,
      `constructionTypeId` varchar(255) DEFAULT NULL,
      `heatingTypeId` varchar(255) DEFAULT NULL,
      `constructionDate` datetime(3) DEFAULT NULL,
      `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      `propertyFloorId` varchar(255) DEFAULT NULL,
      `buildingTypeId` varchar(255) DEFAULT NULL,
      `isStock` bit(1) DEFAULT NULL,
      `createdBy` varchar(255) NOT NULL DEFAULT 'SYSTEM',
      `displayReference` varchar(255) DEFAULT NULL,
      `deletedBy` varchar(255) DEFAULT NULL,
      `designationId` varchar(255) NOT NULL DEFAULT 'DOMESTIC',
      PRIMARY KEY (`id`),
      UNIQUE KEY `reference` (`reference`),
      UNIQUE KEY `idx_isStockUnique` (`isStock`,`landlordId`),
      KEY `parentId` (`parentId`),
      KEY `landlordId` (`landlordId`),
      KEY `addressId` (`addressId`),
      KEY `geoId` (`geoId`),
      KEY `typeId` (`typeId`),
      KEY `constructionTypeId` (`constructionTypeId`),
      KEY `heatingTypeId` (`heatingTypeId`),
      KEY `property_propertyFloorId_foreign_idx` (`propertyFloorId`),
      KEY `property_buildingTypeId_foreign_idx` (`buildingTypeId`),
      KEY `idx_propertyId` (`id`),
      KEY `idx_propertyReference` (`reference`),
      KEY `idx_propertyPropertyStatusId` (`statusId`),
      KEY `property_designationId_foreign_idx` (`designationId`),
      CONSTRAINT `property_buildingTypeId_foreign_idx` FOREIGN KEY (`buildingTypeId`) REFERENCES `buildingtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_designationId_foreign_idx` FOREIGN KEY (`designationId`) REFERENCES `propertydesignation` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `property` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_2` FOREIGN KEY (`landlordId`) REFERENCES `landlord` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_3` FOREIGN KEY (`addressId`) REFERENCES `address` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_4` FOREIGN KEY (`geoId`) REFERENCES `geo` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_5` FOREIGN KEY (`typeId`) REFERENCES `propertytype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_6` FOREIGN KEY (`statusId`) REFERENCES `propertystatus` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_7` FOREIGN KEY (`constructionTypeId`) REFERENCES `constructiontype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_ibfk_8` FOREIGN KEY (`heatingTypeId`) REFERENCES `heatingtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `property_propertyFloorId_foreign_idx` FOREIGN KEY (`propertyFloorId`) REFERENCES `propertyfloor` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    

    and

    CREATE TABLE `landlord` (
      `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      `name` varchar(255) NOT NULL,
      `reference` varchar(255) NOT NULL,
      `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      `description` varchar(255) DEFAULT NULL,
      `totalHousingStock` int NOT NULL DEFAULT '1',
      `statusId` varchar(255) NOT NULL,
      `address` varchar(255) DEFAULT NULL,
      `postcode` varchar(255) DEFAULT NULL,
      `contactName` varchar(255) DEFAULT NULL,
      `contactEmail` varchar(255) DEFAULT NULL,
      `contactPhone` varchar(255) DEFAULT NULL,
      `allowPropertyCreation` tinyint(1) NOT NULL DEFAULT '1',
      `createdBy` varchar(255) NOT NULL DEFAULT 'SYSTEM',
      `typeId` varchar(255) DEFAULT NULL,
      `parentReference` varchar(255) DEFAULT NULL,
      `designationId` varchar(255) NOT NULL DEFAULT 'PROVIDER',
      `internal` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      UNIQUE KEY `reference` (`reference`),
      KEY `landlord_statusId_foreign_idx` (`statusId`),
      KEY `landlord_typeId_foreign_idx` (`typeId`),
      KEY `landlord_designationId_foreign_idx` (`designationId`),
      KEY `landlord_parentreferenceId` (`parentReference`,`id`) /*!80000 INVISIBLE */,
      CONSTRAINT `landlord_designationId_foreign_idx` FOREIGN KEY (`designationId`) REFERENCES `landlorddesignation` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `landlord_parentReference_foreign_idx` FOREIGN KEY (`parentReference`) REFERENCES `landlord` (`reference`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `landlord_statusId_foreign_idx` FOREIGN KEY (`statusId`) REFERENCES `landlordstatus` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `landlord_typeId_foreign_idx` FOREIGN KEY (`typeId`) REFERENCES `landlordtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    


  • I tested your query and I found that when it uses the alert_raisedDateEventId index, there is no Using filesort in the EXPLAIN report. That's why it's using that index, so it can read the rows from the index in the same order that you want them sorted, therefore the ORDER BY is a no-op.

    But this means the first row of the EXPLAIN shows it's doing type: index which means an index-scan. It has to read every entry in that index, even though after it does all the joins, many of them might be eliminated.

    The other joins are all type: eq_ref which means joining to the respective tables' primary keys. This is an efficient way to do the joins, and the optimizer prefers it. So the combination of no filesort and joins by primary key makes the optimizer happy.

    Try this query:

    explain select `event`.`id` as `eventId`
    from `landlord`
    inner join `property` on `landlord`.`id` = `property`.`landlordId`
    inner join `event` on `property`.`id` = `event`.`propertyId`
    inner join `alert` on `event`.`id` = `alert`.`eventId`
    where `landlord`.`reference` in (
         'landlord1',
         'landlord2',
         'landlord3',
         'landlord4',
         'landlord5',
         'landlord6',
         'landlord7',
         'landlord8'
    )
    order by `alert`.`raiseddate` desc limit 20
    

    landlord should use its reference index to make that condition be a range lookup, to select a subset of the rows in landlord, and then the subsequent tables are joined to that result. The joins will be by non-primary indexes, but that might be okay.

    But it will also force alert to be the last table joined, and you can't get rid of the filesort in that case. You can only optimize the filesort away if the order is based on an indexed column of the first table in the series of joins. This might not be a problem if the result set after all the joins have been applied is pretty small.

    You might have to use from landlord force index(reference) if the optimizer tries to reorder the tables back to the original plan.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2