а также
аюсь оптимизировать этот запрос, который сортируетposts
поreputation
поле (1-е), а затемid
поле (2-е). Без 1-го поля запрос занимает ~ 0,250 с, но с этим до ~ 2,500 с (значит, в 10 раз медленнее, ужасно). Любое предложение?
SELECT -- everything is ok here
FROM posts AS p
ORDER BY
-- 1st: sort by reputation if exists (1 reputation = 1 day)
(CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY)
THEN +p.reputation ELSE NULL END) DESC, -- also used 0 instead of NULL
-- 2nd: sort by id dec
p.id DESC
WHERE p.status = 'published' -- the only thing for filter
LIMIT 0,10 -- limit provided as well
Примечания:
- Использование InnoDB (MySQL 5.7.19)
- Первичныйid
наposts
Таблица
- поля индексируются какcreated_at
а такжеreputation
Объясните результат:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra # '1', 'SIMPLE', 'p', NULL, 'ALL', NULL, NULL, NULL, NULL, '31968', '100.00', 'Using filesort'
UPDATE ^^
Репутация обеспечивает следующее: пост, сколько (n = репутация) дней может отображаться в верхней части списка.
На самом деле, я пытался дать репутацию некоторым постам, которые можно было бы найти в верхней части списка, и нашел это решение:Заказывать сообщения по "rep", но только за "один" лимит, Но через некоторое время (около 2 лет) это решение стало проблемой из-за увеличения объема табличных данных. Если я не могу решить эту проблему, я должен удалить эту функцию из службы.
UPDATE ^^
-- all date's are unix timestamp (bigint)
SELECT p.*
, u.name user_name, u.status user_status
, c.name city_name, t.name town_name, d.name dist_name
, pm.meta_name, pm.meta_email, pm.meta_phone
-- gets last comment as json
, (SELECT concat("{",
'"id":"', pc.id, '",',
'"content":"', replace(pc.content, '"', '\\"'), '",',
'"date":"', pc.date, '",',
'"user_id":"', pcu.id, '",',
'"user_name":"', pcu.name, '"}"') last_comment_json
FROM post_comments pc
LEFT JOIN users pcu ON (pcu.id = pc.user_id)
WHERE pc.post_id = p.id
ORDER BY pc.id DESC LIMIT 1) AS last_comment
FROM posts p
-- no issues with these
LEFT JOIN users u ON (u.id = p.user_id)
LEFT JOIN citys c ON (c.id = p.city_id)
LEFT JOIN towns t ON (t.id = p.town_id)
LEFT JOIN dists d ON (d.id = p.dist_id)
LEFT JOIN post_metas pm ON (pm.post_id = p.id)
WHERE p.status = 'published'
GROUP BY p.id
ORDER BY
-- everything okay until here
-- any other indexed fields makes query slow, not just "case" part
(CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY)
THEN +p.reputation ELSE NULL END) DESC,
-- only id field (primary) is effective, no other indexes
p.id DESC
LIMIT 0,10;
Объясните;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra 1, PRIMARY, p, , ref, PRIMARY,user_id,status,reputation,created_at,city_id-town_id-dist_id,title-content, status, 1, const, 15283, 100.00, Using index condition; Using temporary; Using filesort # dunno, these join's are not using, but if i remove returning fields from select part show "Using index condition" 1, PRIMARY, u, , eq_ref, PRIMARY, PRIMARY, 2, p.user_id, 1, 100.00, 1, PRIMARY, c, , eq_ref, PRIMARY, PRIMARY, 1, p.city_id, 1, 100.00, 1, PRIMARY, t, , eq_ref, PRIMARY, PRIMARY, 2, p.town_id, 1, 100.00, 1, PRIMARY, d, , eq_ref, PRIMARY, PRIMARY, 2, p.dist_id, 1, 100.00, 1, PRIMARY, pp, , eq_ref, PRIMARY, PRIMARY, 2, p.id, 1, 100.00, 2, DEPENDENT SUBQUERY, pc, , ref, post_id,visibility,status, post_id, 2, func, 2, 67.11, Using index condition; Using where; Using filesort 2, DEPENDENT SUBQUERY, pcu, , eq_ref, PRIMARY, PRIMARY, 2, pc.user_id, 1, 100.00,