Laravel SQL SELECT 100% CPU

Question

following problem with SELECTS like this only in staging system:

SELECT * FROM `nd` 
WHERE `nd`.`nd_id` IN (1, 396, 782, 830, 831, 1929, 3786, 5059, 5713, 5714, 6698) 
AND `nd`.`nd_type` = 'App\Models\N' 
AND `nd`.`deleted_at` 
IS NULL

In my dev system the select needs 340 ms. In my staging system (other server) all CPU go up to 100% and never finish the selects. Same problem when I start this query direct in PHPMyAdmin in staging system.

How I can do? Some settings in config wrong?

Maybe the problem is: After deployment to staging the migration for set keys in ndaten table was not completed. The first key was set and migration execution stops. Repair and optimize the table already done.

Thanks for help :)

Answer 1

This composite index is likely to help both staging and production:

INDEX(nd_type, deleted_at, nd_id)

By "the migration for set keys in ndaten table", do you mean that staging and production did not have the same indexes? If so, that violates a main intent of staging. (I don't know laravel's "migration" features, so I cannot address details, but this sounds vital!)

If, after dealing with both of those, you still have problems, please provide

SHOW CREATE TABLE
the size of the relevant table(s)
the query
EXPLAIN SELECT ...
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- on both