Filesort instead of index scan

Question

I migrated my PHP/MySQL DB from Unix to Windows. I dumped the DB and imported it directly on Windows without any difference. I use the same script/PHP version.

My Table is:

CREATE TABLE `pximg` (
  `ppoc` tinyint unsigned NOT NULL,
  `file` int unsigned NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `img` mediumblob,
  PRIMARY KEY (`ppoc`,`file`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

My PHP script to reproduce:

// SQL queries
$queryNoBindings = "EXPLAIN SELECT img, ts FROM pximg WHERE ppoc = 0 ORDER BY `file` DESC LIMIT 1";
$queryBindings   = "EXPLAIN SELECT img, ts FROM pximg WHERE ppoc = ? ORDER BY `file` DESC LIMIT 1";
$ppoc = 0;

// connect
$m = new mysqli('127.0.0.1', $dbData['px'][1], $dbData['px'][2], $dbData['px'][3], $dbData['px'][4]);

// query with no bindings
$q = $m->query($queryNoBindings);
$r1 = $q->fetch_array(MYSQLI_ASSOC);
print_r($r1); 

// query with bindings
$stmt = $m->prepare($queryBindings);
$stmt->bind_param('i', $ppoc);                
$stmt->execute();
$r = $stmt->get_result();
$r2 = $r->fetch_array(MYSQLI_ASSOC);
print_r($r2); 

The result on Unix is

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 385758
    [filtered] => 100.00
    [Extra] => Backward index scan
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 385758
    [filtered] => 100
    [Extra] => Backward index scan
)

The result on Windows is

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 370682
    [filtered] => 100.00
    [Extra] => Backward index scan
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 370682
    [filtered] => 100
    [Extra] => Using filesort
)

So the difference is: when I use the query with bindings on Windows, it is now using filesort instead of Backward index scan and takes about 20s instead of 0.02s.

Has anyone an idea why? Or, if filesort is ok, why the query is so incredible slow now?

Answer 1

It looks like your newly loaded server isn't using your primary key.

Here are some things to try:

1. SHOW TABLE piximg; on your new server shows you the definition of your bulk-loaded table. Did the primary key definition get into your new table? If not, do this.

    ALTER TABLE piximg ADD PRIMARY KEY(`ppoc`, `file`);
   

2. ALTER TABLE pximg ENABLE KEYS; on the off chance that your bulk load operation disabled key handling.

3. OPTIMIZE TABLE pximg; because sometimes newly bulk loaded tables have index statistics that don't make sense until optimized.

4. For this query's specific situation, change your index definition slightly, to include the DESC qualifier on the file column.

    PRIMARY KEY (`ppoc`, `file` DESC)
   

5. The pattern SELECT blah, blah, blob ... ORDER BY blah DESC LIMIT 1 is a notorious performance antipattern. You may want to try this instead.

SELECT a.img, a.ts 
  FROM pximg a
  JOIN (
              SELECT ppoc, MAX(file) file
                FROM piximg
               GROUP BY ppoc
       ) b ON a.ppoc = b.ppoc AND a.file = b.file
 WHERE ppoc = <<<whatever>>>

This will avoid making the MySQL server slurp up and sort a lot of rows with big fat Blobs in them, only to discard all but one.