[Solved] MySQL QUERY in preparing for too long

EverSQL Database Performance Knowledge Base

MySQL QUERY in preparing for too long

Database type:

The following SQL has a preparing time of 30+ second. Is the SQL which is wrong, or the fact that I have close to one million result in the database? Can this SQL be optimized not to have it in preparing for that long?

UPDATE url_source_wp SET hash="ASDF2"
WHERE (url_source_wp.id NOT IN (
   SELECT url_done_wp.url_source_wp FROM url_done_wp WHERE url_done_wp.url_group = 4)
)
AND (hash IS NULL) LIMIT 50

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `url_done_wp` ADD INDEX `url_wp_idx_url_group` (`url_group`);
ALTER TABLE `url_source_wp` ADD INDEX `url_wp_idx_hash_id` (`hash`,`id`);
The optimized query:
SELECT
        url_source_wp.hash 
    FROM
        url_source_wp 
    WHERE
        (
            url_source_wp.id NOT IN (
                SELECT
                    url_done_wp.url_source_wp 
                FROM
                    url_done_wp 
                WHERE
                    url_done_wp.url_group = 4
            )
        ) 
        AND (
            url_source_wp.hash IS NULL
        ) LIMIT 50

Related Articles



* original question posted on StackOverflow here.