Optimizing a query that uses a left join

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,
sites_jobs_categories.name,
FROM sites_jobs_categories
LEFT JOIN sites_jobs ON sites_jobs.frn_jobs_categories_id =
sites_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:
> 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,
> sites_jobs_categories.name,
> FROM sites_jobs_categories
> LEFT JOIN sites_jobs ON sites_jobs.frn_jobs_categories_id =
> sites_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'.)

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.

Regards,
Bill K.