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 '%маÑÑив%') 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 '%маÑÑив%'))