4

I have a users table using Laravels Soft Delete which is essentially a null-able deleted_at column. If deleted_at is null, then the user was not deleted.

I want to count the number for non-deleted users of a 130k users table who have been created after a specific date.

Using

`select count(*) as aggregate from `users` where `created_at` >= '2019-09-18 10:33:09' and `users`.`deleted_at` is null`;

takes 269ms.

Using

select count(*) as aggregate from `users` where date(`created_at`) >= '2019-09-18 10:33:09';

takes 57ms.

The table contains 137381 users from which 48 users are soft deleted.

I already found at https://stackoverflow.com/a/26530488/2311074 that deleted_at should not be indexed, and actually indexing deleted_at is not changing anything. Also creating a touple index (created_at, deleted_at) does not change anything.

I was able to imrpove the execution time of the query with

select SUM(CASE WHEN  (deleted_at is null) THEN 1 ELSE 0 END) as aggregate from `users` where date(`created_at`) >= '2019-09-18 10:33:09';

which only executes in 78ms but I don't understand what makes the query 5 times slower with the additional where condition?

Using Mysql Explain didnt help me to find out the issue. Here is the result for the 78ms query

enter image description here

And thats the result for the 269ms query

enter image description here

Adam
  • 25,960
  • 22
  • 158
  • 247
  • are those values the average from multiple tests ? or you just rerun the query like 10 times ? – N69S Sep 18 '20 at 09:26
  • @N69S I executed the query in mysql workbench like 10 times – Adam Sep 18 '20 at 09:33
  • That's not enough to benchmark it correctly, was it on production or on local (make sure no other queries are run at the same time). Because i just run a local test with 300k entries and both request are at max 16ms at minimum 15ms with 0 index – N69S Sep 18 '20 at 09:34
  • @N69S it was on local. There were no other queries in parallel. I am using a docker container. I am on Ubuntu 20 iwth 16GB Memory andA MD® Ryzen 7 4700u . Did you use mysql 5.7? 15mx without index on 300k indexes sounds crazy fast – Adam Sep 18 '20 at 09:43
  • used MySql 5.7.24-winx64 with heidiSQL. – N69S Sep 18 '20 at 11:08

0 Answers0