Why COUNT is not counting?

Can't understand why this is happening.

The query outputs the data (several lines):

SELECT
a.*
FROM 
 `catalog_products` a, 
 `catalog_categories` b ,
 `catalog_products_63` e
WHERE
 a.`category_id` = b.`id`
and
 b.`id` = 1678
and
 a.`brand_id` = 336
and
 a.`id` = e.`product_id`
 and 
 a.`status` = 1


And this shows that the number 0:

SELECT
COUNT(a.`id`)
FROM 
 `catalog_products` a, 
 `catalog_categories` b ,
 `catalog_products_63` e
WHERE
 a.`category_id` = b.`id`
and
 b.`id` = 1678
and
 a.`brand_id` = 336
and
 a.`id` = e.`product_id`
 and 
 a.`status` = 1


But then everything starts to work as it should:

OPTIMIZE TABLE `catalog_products`

What could be wrong?

Thank you
June 10th 19 at 16:03
1 answer
June 10th 19 at 16:05
All the problem is found. On the field category_id was single and compound index. Delete single index regained the correctness of the queries. Don't duplicate indexes))

Find more questions by tags SQLMySQL