Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4410

update does not want to use a covering index, but select uses it.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.1, 5.5.30, 5.3.12
    • Fix Version/s: 5.5.37, 10.0.10, 5.3.13
    • Component/s: None
    • Environment:
      innodb_version 5.5.30-MariaDB-30.1
      version 5.5.30-MariaDB-log
      version_comment MariaDB Server
      version_compile_machine x86_64
      version_compile_os Linux

      Description

      Good day!

      I found problem and strange behavior of update

      there is table

      CREATE TABLE `schedule_test` (
         `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
         `datetime_processed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
         `datetime_next` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
         `sign` bigint(20) unsigned NOT NULL DEFAULT '0',
         `lock` tinyint(4) NOT NULL DEFAULT '0',
         PRIMARY KEY (`id`),
         KEY `idx_lock_dt_nxt` (`lock`,`datetime_next`)
      ) ENGINE=InnoDB;

      it's a simple task scheduler and table has near 45k rows

      Task selecting doing in this way:

      update schedule_test as sa 
      set 
           sa.`lock` = 1, 
           sa.sign = 2123123123,
           sa.`datetime_processed` = now()
      where 
             sa.`lock` = 0 
      and sa.`datetime_next` <= now()
      order by sa.`datetime_next`  asc
      limit 10;

      problem is that update doesnt use index `idx_lock_dt_nxt` for selecting and mysql does table fullscan (read all 45k).
      I look in innotop count readed rows.

      But if rewrite update to select, it works as should - use undex and innodb read from table only 10 rows.

      select sa.`lock`, sign, datetime_next
      from  schedule_test as sa 
      where 
             sa.`lock` = 0 
      and sa.`datetime_next` <= now()
      order by sa.`datetime_next`  asc
      limit 10;

      explain of this:

      id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
      1    SIMPLE    sa    ref    lock    lock    1    const    22716    Using index condition; Using where

      for me is not understandable nature this behaviour. as i know select and update should use the same execution plan.

      As an experiment, i modify index and make it so KEY `idx_lock_dt_nxt` (`datetime_next`)

      in this case update begins using it for select rows by date, but i keep in my mind, it works like full scan but little softer, anyway.
      because after some time all rows with minimal datetime will be locked (lock=1) and mysql have to scan more and more rows for checking where condition.

      i think may be there is an error in choosing good query plan execution for update?

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            boa Oleg (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: