How to Efficiently Limit Results Per Company When Using UNION in MySQL?

Optimizing MySQL Queries with UNION and LIMIT for Multi-Company Job Searches

Efficient data retrieval from large databases requires careful query construction. This example focuses on fetching job listings from multiple companies, limiting the results to a maximum of 10 jobs per company.

A naive approach using UNION DISTINCT with a single LIMIT clause will restrict the total number of results, not the results per company. The solution lies in applying ORDER BY and LIMIT within each individual SELECT statement of the UNION query:

(
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 IN ('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 IN ('Company2')
ORDER BY name, title
LIMIT 0, 10
);

This revised query ensures that the LIMIT clause operates independently on each company's job data, returning up to 10 jobs per company. Note the use of UNION ALL which is generally more efficient than UNION DISTINCT unless distinct results are explicitly required. This approach handles cases where a company has fewer than 10 jobs, returning all available jobs for that company.

For enhanced flexibility and more complex scenarios, consider leveraging MySQL's ROW_NUMBER() window function. ROW_NUMBER() assigns a unique rank to each row within a defined partition (in this case, per company), enabling sophisticated result filtering and grouping.

The above is the detailed content of How to Efficiently Limit Results Per Company When Using UNION in MySQL?. For more information, please follow other related articles on the PHP Chinese website!