Question 21144 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL 8 slow Query cause deprecated?

I find this query and that takes 20 seconds for few rows…

SELECT DISTINCT(BINARY `meta_key`) as meta_key_binary, `meta_key`
FROM wp_postmeta
ORDER BY `meta_key` ASC

Is that deprecated in mysql 8? How to change it for mysql 8 than?
Or any idea why that is so slow?

I cant find something nearly same for mysql 8

Tags:

2

Answers


  1. 0

    Using the BINARY conversion operator is probably preventing use of an index for your query.

    You could add a virtual column to the table with the binary value of meta_key and index that.

    ALTER TABLE wp_postmeta 
    ADD COLUMN meta_key_binary VARCHAR(100) AS (BINARY meta_key),
    ADD INDEX (meta_key_binary, meta_key);

    Replace (100) with the correct length of meta_key from the original table schema.

    Login or Signup to reply.
  2. 0

    —– 2021-10-19 8.0.27 General Availability — — Important Change —–

    The BINARY operator is now deprecated, and subject to removal in a future release of MySQL. Use of BINARY now causes a warning. Use CAST(… AS BINARY) instead.

    See Barmar’s Comment on parentheses

    For improved performance of WP, especially when using postmeta: WP Index Improvements

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search