Optimizing a query that uses a left join

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi, I've spent hours trying to optimize this simple query:

SELECT      count(sites_jobs.id) as jobCount,
sites_jobs_categories.frn_site_id, sites_jobs_categories.id,
FROM         sites_jobs_categories
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
'possible keys'.)

Any help or pointer would be much appreciated!

Re: Optimizing a query that uses a left join

mamapossible@googlemail.com wrote:
Quoted text here. Click to load it

Try putting the condition in a WHERE clause instead of a HAVING clause.
  Traditionally, WHERE is for restricting rows, HAVING is for
restricting groups.

(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
WHERE clause.)

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.

Bill K.

Site Timeline