The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
SELECT COUNT(DISTINCT p.productID) as cnt FROM SC_products p LEFT JOIN SC_tagged_objects t2 ON p.productID=t2.object_id LEFT JOIN SC_tags t1 ON t2.tag_id=t1.id AND t2.object_type="product" WHERE ( ( LOWER(t1.name) LIKE '%мебелÑ%' OR LOWER(t1.name) LIKE '%длÑ%' OR LOWER(t1.name) LIKE '%ÑпалÑни%') AND categoryID<>1 AND enabled=1) OR (categoryID<>1 and enabled=1 AND ( LOWER(`name_ru`) LIKE '%мебелÑ%' OR LOWER(`description_ru`) LIKE '%мебелÑ%' OR LOWER(`brief_description_ru`) LIKE '%мебелÑ%') AND ( LOWER(`name_ru`) LIKE '%длÑ%' OR LOWER(`description_ru`) LIKE '%длÑ%' OR LOWER(`brief_description_ru`) LIKE '%длÑ%') AND ( LOWER(`name_ru`) LIKE '%ÑпалÑни%' OR LOWER(`description_ru`) LIKE '%ÑпалÑни%' OR LOWER(`brief_description_ru`) LIKE '%ÑпалÑни%'))