Fix mysql query

DELETE FROM keywords
WHERE NOT EXISTS
(SELECT keywords_relations.k_id FROM keywords_relations WHERE keywords.k_id = keywords_relations.k_id)

It is taking too long...I have 583,000 keywords (utf_unicode) and 1million keywords_relations. In past the query used to happen in 20-60 seconds, but I try running it now and it hasnt happened in half an hour.

Could you please suggest what might be wrong. Also, any other alterantives to this query.

I am trying to delete the keywords from the keywords table whose id dont exist in the keywords relations table.

Thanks

The site is http://domainsoutlook.com/

You can try and going on it and also see that all the queries are running开发者_JAVA百科 slowly.

PS. The server crashed about a few days ago and a fsck check or something was carried out on the disk by my server maintenance support.

Indexes on keywords = k_id(primary), keywords(unique) indexes on keywords_relations = k_id(index)

try this instead and see if it makes a difference:

DELETE keywords
    FROM keywords
        LEFT JOIN keywords_relations
            ON keywords.k_id = keywords_relations.k_id
    WHERE keywords_relations.k_id IS NULL

WHERE NOT EXISTS (subquery) is known to cause performance issues in MySQL <5.4. Use LEFT JOIN instead

WARNING: Test it before running on live database. I claim no responsibility for data lost.

DELETE 
  k
FROM 
  keywords AS k
LEFT JOIN
  keywords_relations AS kr
USING (k_id)
WHERE
  kr.k_id IS NULL

I assume that you don't want to delete keywords, if there are one or more keyword_relations belonging to it. So the first thing you could do is adding a LIMIT 1 to your SELECT-Query.

You sure have set indexes for k_id, right? If yes, this actually shouldn't be a thing for MySQL...