Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Optimizing a query that uses a left join
May 7, 2006, 2:42 pm
rate this thread
SELECT count(sites_jobs.id) as jobCount,
LEFT JOIN sites_jobs ON sites_jobs.frn_jobs_categories_id =
GROUP BY sites_jobs_categories.id
HAVING sites_jobs_categories.frn_site_id = #request.siteId#
ORDER BY sites_jobs_categories.name ASC
I placed an index on sites_jobs_.frn_jobs_categories_id, which gave me
a nice speed boost, but for some reason mysql won't use the index on
sites_jobs_categories.frn_site_id (explains states that there are no
Any help or pointer would be much appreciated!
Re: Optimizing a query that uses a left join
Try putting the condition in a WHERE clause instead of a HAVING clause.
Traditionally, WHERE is for restricting rows, HAVING is for
(In spite of the fact that some people put row conditions in the HAVING
clause if they need to reference a column alias defined in the
select-list, because SQL says that we can't use column aliases in the
Anyway, it might be easier for the optimizer to use an index for the
WHERE clause than the HAVING clause.
Also keep in mind that MySQL has a limitation of using only one index
per table in a given query. It might have decided that using an index
on sites_jobs_categories.name (for the ORDER BY) is of greater overall
benefit than using an index on frn_site_id. That is, if you have an
index on that name column.
- » Which is the right Directory to backup MySQL database on Linux Opearing system?
- — Next thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum