title: Optimize MySQL UPDATE query that contains WHERE and ORDER BY?
body: <p>How can I optimize this query?  If I run it without the ORDER BY clause, it executes in &lt;100ms.  With the ORDER BY clause it takes many seconds, and crushes the server when more than one system is trying to make this query at once.</p>
  
  <pre><code>UPDATE companies
  SET
      crawling = 1
  WHERE
      crawling = 0
      AND url_host IS NOT NULL
  ORDER BY
      last_crawled ASC
  LIMIT 1;
  </code></pre>
  
  <p>If I run this query as a SELECT, it's also fast ( &lt;100ms ).</p>
  
  <pre><code>SELECT id
  FROM companies
  WHERE
      crawling = 0
      AND url_host IS NOT NULL
  ORDER BY
      last_crawled ASC
  LIMIT 1;
  </code></pre>
  
  <p>Here is my table schema:</p>
  
  <pre><code>CREATE TABLE `companies` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `url` varchar(255) DEFAULT NULL,
    `url_scheme` varchar(10) DEFAULT NULL,
    `url_host` varchar(255) DEFAULT NULL,
    `name` varchar(255) DEFAULT NULL,
    `crawl` tinyint(1) unsigned NOT NULL DEFAULT '1',
    `crawling` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `last_crawled` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `url_host` (`url_host`),
    KEY `crawl` (`crawl`),
    KEY `crawling` (`crawling`),
    KEY `last_crawled` (`last_crawled`),
    KEY `url_scheme` (`url_scheme`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <hr>
  
  <p><strong>UPDATE ONE</strong></p>
  
  <p>This query gives me the following error: <code>You can't specify target table 'companies' for update in FROM clause</code></p>
  
  <pre><code>UPDATE companies
  SET crawling = 1
  WHERE id = (
      SELECT id
      FROM companies
      WHERE
          crawling = 0
          AND url_host IS NOT NULL
      ORDER BY
          last_crawled ASC
      LIMIT 1
  );
  </code></pre>
  
  <p>This query gives me the following error: <code>This version of MySQL doesn't yet support 'LIMIT &amp; IN/ALL/ANY/SOME subquery'</code></p>
  
  <pre><code>UPDATE companies
  SET crawling = 1
  WHERE id in (
      SELECT id
      FROM companies
      WHERE
          crawling = 0
          AND url_host IS NOT NULL
      ORDER BY
          last_crawled ASC
      LIMIT 1
  );
  </code></pre>