Oh, the classic problem.
Can you get rid of
AND (KeywordsRequest.status = 'success' OR KeywordsRequest.status = 'active')
? It has two problems:
* OR -- this usually eliminates use of indexes
* Those values are probably rarely 'fail' or 'inactive', yet the engine has to do a lot of work to get around them.
"KeywordsRequest.id IS NULL" may fail into the above issue.
Rows_sent: 568 Rows_examined: 1826718 --
This is a big clue. It says it can't really use the index, but has to gather 1.8M rows before filtering things down to 568.
"I've got indexes on almost every column" -- not good:
* More indexes -> more time on INSERTing
* Sounds like single-column indexes. You possibly need a "compound" index.
Have you tried INDEX (source_id, created) ? (I can't see enough to know if that is optimal.)
Why do you have
GROUP BY Keyword.id
? Perhaps that can be eliminated? Or replaced by SELECT DISTINCT?
See if this gives the same result and is faster:
SELECT DISTINCT k.id, k.keyword
FROM `keywords` as k
JOIN
( SELECT keyword_id, created
FROM `keywords_requests`
WHERE (status = 'success' OR status = 'active')
AND source_id = '29'
AND created > FROM_UNIXTIME(1234551323)
AND id IS NULL
) AS kr
WHERE kr.keyword_id = k.id
ORDER BY kr.created ASC;
For further analysis of this slow SELECT, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]