Optimizing a query that uses a left join

[mamapossible]

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

SELECT count(sites_job s.id) as jobCount,
sites_jobs_cate gories.frn_site _id, sites_jobs_cate gories.id,
sites_jobs_cate gories.name,
FROM sites_jobs_cate gories
LEFT JOIN sites_jobs ON sites_jobs.frn_ jobs_categories _id =
sites_jobs_cate gories.id
GROUP BY sites_jobs_cate gories.id
HAVING sites_jobs_cate gories.frn_site _id = #request.siteId #
ORDER BY sites_jobs_cate gories.name ASC

I placed an index on sites_jobs_.frn _jobs_categorie s_id, which gave me
a nice speed boost, but for some reason mysql won't use the index on
sites_jobs_cate gories.frn_site _id (explains states that there are no
'possible keys'.)

Any help or pointer would be much appreciated!

[Bill Karwin]

ma**********@go oglemail.com wrote:

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

SELECT count(sites_job s.id) as jobCount,
sites_jobs_cate gories.frn_site _id, sites_jobs_cate gories.id,
sites_jobs_cate gories.name,
FROM sites_jobs_cate gories
LEFT JOIN sites_jobs ON sites_jobs.frn_ jobs_categories _id =
sites_jobs_cate gories.id
GROUP BY sites_jobs_cate gories.id
HAVING sites_jobs_cate gories.frn_site _id = #request.siteId #
ORDER BY sites_jobs_cate gories.name ASC

I placed an index on sites_jobs_.frn _jobs_categorie s_id, which gave me
a nice speed boost, but for some reason mysql won't use the index on
sites_jobs_cate gories.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_cate gories.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.