MySQL loads 100% CPU in a minute using SELECT in SELECT

Question

Situation

My SQL query contains two parts. The first is very simple:

SELECT * FROM `clients` WHERE id IN (...)

This information will return all information about clients with specific identifiers.

The second request is much larger, it should prepare a list of identifiers for the previous request (I skipped a few UNION for better readability, you can see the full request at the end):

SELECT client_id
  FROM `contact_persons`
  WHERE id IN (
              SELECT owner_id
                FROM `contacts`
                WHERE contact_info LIKE '%keyword%' AND company_or_person = 'person'
  )
UNION
SELECT owner_id
  FROM `contacts`
  WHERE contact_info LIKE '%keyword%' AND company_or_person = 'company'
UNION
SELECT id
  FROM `clients`
  WHERE client_name LIKE '%keyword%'

Each of these queries takes less than 0.5 seconds to execute separately. But when I insert the second request into the first, it forces them to execute for more than a minute to complete the processor load and up to 100% during this time.

What i tried

• Run both parts of this query separately. Result: everything is fast enough (less than 0.5 seconds).
• SELECT * ... SELECT id ... ( ). :
• , .
• (), 0 . : . .

, php, , , .

SELECT * FROM `clients` WHERE deleted = 0 AND id IN (
SELECT client_id as found
  FROM `contact_persons`
  WHERE id IN (
              SELECT owner_id
                FROM `contacts`
                WHERE contact_info LIKE '%keyword%' AND company_or_person = 'person'
 )
UNION
SELECT owner_id as found
  FROM `contacts`
  WHERE contact_info LIKE '%keyword%' AND company_or_person = 'company'
UNION
SELECT id as found
  FROM `clients`
  WHERE client_name LIKE '%keyword%'
UNION
SELECT client_id as found
  FROM `cargo`
  WHERE cargo_name LIKE '%keyword%'
UNION
SELECT page_id as found
  FROM `comments`
  WHERE message LIKE '%keyword%' AND page_type = 'client' AND deleted = 0
UNION
SELECT client_id as found
  FROM `contact_persons`
  WHERE person_name LIKE '%keyword%')

(160 Kb), clients 160 5 . , , .

1. EXPLAIN

EXPLAIN

2. UNION

, UNION. : - 57

SELECT SQL_NO_CACHE id FROM `clients` WHERE id IN (
 SELECT client_id
   FROM `contact_persons`
   WHERE id IN (
               SELECT owner_id
                 FROM `contacts`
                 WHERE contact_info LIKE '%keyword%'
             )
)

3.

, IN INNER JOIN, , ~ 60 ~ 0,4

,

SELECT .. WHERE .. IN (SELECT .. WHERE .. IN (SELECT ..))

SELECT .. AS t1 INNER JOIN (SELECT .. WHERE .. IN (SELECT ..)) AS t2 WHERE ti.id = t2.id

, , MySQL.

- , EXPLAIN :

:

Answer 1

, , , . EXPLAIN , @huhushow, , .

, .

• , . , "". , .

• INSERT IGNORE found . JOIN .

• , SQL, . , mysqld (, ), , ; , , .

• , SELECT , UNION; ..

  SELECT a.* WHERE id IN ( select1 UNION select2 UNION select3 )
  

  - " " , id ,

  SELECT a.* WHERE id IN ( select1 )
  UNION
  SELECT a.* WHERE id IN ( select2 )
  UNION
  SELECT a.* WHERE id IN ( select3 )
  

  , , IN, JOIN. , :

  SELECT * FROM `clients` WHERE deleted = 0 AND id IN (
      SELECT client_id as found
      FROM `contact_persons`
      WHERE id IN (
        SELECT owner_id
          FROM `contacts`
          WHERE contact_info LIKE '%keyword%' AND company_or_person
          = 'person' )
  

  :

  SELECT * FROM `clients` WHERE deleted = 0 AND id IN (
      SELECT cp.client_id as found
      FROM `contact_persons` AS cp
      JOIN `contacts` AS c ON (cp.id = c.owner_id)
          WHERE c.contact_info LIKE '%keyword%' AND c.company_or_person
          = 'person' )
  )
  

  , , , :

  SELECT [DISTINCT] clients.* FROM `clients`
      JOIN `contact_persons` AS cp ON (clients.id = cp.client_id)
      JOIN `contacts` AS c ON (cp.id = c.owner_id)
           WHERE clients.deleted = 0 
           AND c.contact_info LIKE '%keyword%'
           AND c.company_or_person = 'person'
  

  : , , owner_id contacts company_or_person ( =) contact_info. ,

  CREATE INDEX contacts_ndx_1 ON contacts(company_or_person, contact_info, owner_id)
  

  , , .

Answer 2

.

EXPLAIN SELECT * FROM `clients` WHERE deleted = 0 AND id IN (
SELECT client_id as found
  FROM `contact_persons`
  WHERE id IN (
              SELECT owner_id
                FROM `contacts`
                WHERE contact_info LIKE '%keyword%' AND company_or_person = 'person'
 )
UNION
SELECT owner_id as found
  FROM `contacts`
  WHERE contact_info LIKE '%keyword%' AND company_or_person = 'company'
UNION
SELECT id as found
  FROM `clients`
  WHERE client_name LIKE '%keyword%'
UNION
SELECT client_id as found
  FROM `cargo`
  WHERE cargo_name LIKE '%keyword%'
UNION
SELECT page_id as found
  FROM `comments`
  WHERE message LIKE '%keyword%' AND page_type = 'client' AND deleted = 0
UNION
SELECT client_id as found
  FROM `contact_persons`
  WHERE person_name LIKE '%keyword%')

, . mysql .