How to Efficiently Combine UNION and LIMIT in MySQL to Retrieve Data from Multiple Tables?

Optimizing MySQL Queries: Combining UNION and LIMIT for Multi-Table Data Retrieval

Retrieving data from multiple tables often requires combining results using UNION and limiting the number of rows with LIMIT. This guide demonstrates an efficient approach to achieve this.

Consider a scenario where you need to extract a total of 20 job listings from two specific companies, with a maximum of 10 jobs per company. The following optimized query accomplishes this:

(
  SELECT c.name, j.title, j.`desc`, j.link 
  FROM jobs_job j
  INNER JOIN companies_company c ON j.company_id = c.id
  WHERE c.name = 'Company1'
  ORDER BY name, title
  LIMIT 0, 10
) UNION ALL
(
  SELECT c.name, j.title, j.`desc`, j.link 
  FROM jobs_job j
  INNER JOIN companies_company c ON j.company_id = c.id
  WHERE c.name = 'Company2'
  ORDER BY name, title
  LIMIT 0, 10
)

The key to efficiency lies in placing LIMIT within each individual SELECT statement. This ensures that each company's results are independently limited to 10 rows. The UNION ALL operator (used here for speed; UNION would remove duplicates) then combines these limited result sets, guaranteeing a maximum of 20 rows in the final output.

The ORDER BY clause within each subquery maintains sorted results by company name and job title before the union operation. This ensures the final combined result set remains sorted. This method is far more efficient than applying LIMIT after the UNION operation, which would require processing all results before applying the limit.

The above is the detailed content of How to Efficiently Combine UNION and LIMIT in MySQL to Retrieve Data from Multiple Tables?. For more information, please follow other related articles on the PHP Chinese website!