Почему Mysql долго вычисляет COUNT?
Не слышал о таком ограничении (впрочем, я не большой специалист в mysql). Возможно, просто планировщик решил на 8 условии, что быстрее будет sequence scan.

Попробуйте переписать запрос эквивалентно:
select sum(c) from (
  select count(id) as c from mytable where col1=609
  union
  select count(id) from mytable where col2=609
  union
  ...
)
1 row in set (0.00 sec) - ~30 000 000 записей
SELECT 
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 12) AS `count_01`,	
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 123) AS `count_02`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 1234) AS `count_03`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 12345) AS `count_04`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 123456) AS `count_05`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 1234567) AS `count_06`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 7654321) AS `count_07`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 765432) AS `count_08`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 76543) AS `count_09`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 7654) AS `count_10`,    
	(SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 765) AS `count_11`,    
    (SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 76) AS `count_12`


8 rows in set (0.02 sec) - ~30 000 000 записейSELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 543 
    UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 5443
        UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 34567
            UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 211567
                UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 345688
                    UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 123356
                        UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 1433556
                            UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 2355464
                            	UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 566967
                                	UNION SELECT COUNT(`id`) FROM `user_shmots` WHERE `user_id` = 123133