0

I'm trying to implement soft-delete so I added deleted_at (datetime) column to users table. Also there is a query i need to run:

    SELECT 
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
GROUP BY `users`.`category_id`;



type, possible_keys, key, key_len, ref, rows, filtered, Extra
'index', 'index_users_on_category_id', 'index_users_on_category_id', '5', NULL, '840926', '100.00', 'Using index'

which was running just fine until i added WHERE clause

    SELECT 
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
WHERE
    `users`.`deleted_at` IS NULL
GROUP BY `users`.`category_id`;

type, possible_keys, key, key_len, ref, rows, filtered, Extra
'ref', 'index_users_on_category_id,index_users_on_deleted_at', 'index_users_on_deleted_at', '6', 'const', '420463', '100.00', 'Using index condition; Using temporary; Using filesort'

Of course it becomes slower, i can't find information

  1. why type changed
  2. why key_len changed from 5 to 6
  3. why rows in second query shows half of total number (800k)

And how can i make it faster without using USE INDEX (index_users_on_category_id)

  • Try to create index by (deleted_at, category_id). – Akina Jan 10 at 7:30
  • I added multi-column index, however it didn't help, it still use index_users_on_deleted_at as default one – Alexandr Climov Jan 10 at 7:52
  • As an experiment - replace SELECT COUNT(*) AS count_all, ... WHERE `users`.`deleted_at` IS NULL with SELECT COUNT(*) - COUNT(`users`.`deleted_at`) AS count_all, ... WHERE 1... and test it with above complex index and with (category_id, deleted_at) index. – Akina Jan 10 at 8:17
0

this index actually helped: deleted_at, category_id, however, I tried to do this without adding new index Since there are much more records where deleted_at is null, i tried next queries:

SELECT 
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
WHERE
     `users`.`id` NOT IN (SELECT `users`.`id` FROM `users` WHERE `users`.`deleted_at` IS NOT NULL)
GROUP BY `users`.`category_id`;

And query optimizer used much better schema. I will try it soon on big set and hoe it will work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.