Page 1 of 1

help to optimize query

Posted: Sat Mar 18, 2006 1:33 pm
by newmember
Hi

here is a query:

(mysql 4.1)
$qr="EXPLAIN SELECT image_id, COUNT(*) AS totalcomments FROM image LEFT JOIN comment ON comment_imageId=image_id WHERE image_galleryId='".$id."' GROUP BY image_id";
this query selects images from specific gallery and also for each image counts how many comments there are

here is what EXPLAIN tells:
id: 1
select_type: SIMPLE
table: image
type: ref
possible_keys: image_galleryId
key: image_galleryId
key_len: 4
ref: const
rows: 41
extra: Using where; Using index; Using temporary; Using filesort


id: 1
select_type: SIMPLE
table: comment
type: ref
possible_keys: comment_imageId
key: comment_imageId
key_len: 4
ref: test.image.image_id
rows: 1
extra: Using index

there are indexes on comment_imageId and image_galleryId.
I also verified that Using filesort and Using temporary caused by

Code: Select all

image_galleryId='".$id."'
clause, if for example instead i would write

Code: Select all

image_id='".$id."'
then Using filesort or Using temporary no more appear..

as i see it, there is not really a need to do Using filesort or Using temporary
i mean,for each image retrieved according to

Code: Select all

image_galleryId='".$id."'
using only image_galleryId index, search in comment table using comment_imageId index...the same index also allows directly calculate COUNT(*) for result row.
no need to do file sorting and no need to copy comment rows matched in GROUP BY into temporary table

I really don't understand why mysql uses filesort and temporary table... :?

Do you know of a way to prevent file sorting and usage of temporary table?

Thanks