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.

New contributor
Alexandr Climov is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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