help to optimize query
Posted: Sat Mar 18, 2006 1:33 pm
Hi
here is a query:
(mysql 4.1)
here is what EXPLAIN tells:
there are indexes on comment_imageId and image_galleryId.
I also verified that Using filesort and Using temporary caused by clause, if for example instead i would write 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 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
here is a query:
(mysql 4.1)
this query selects images from specific gallery and also for each image counts how many comments there are$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";
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."'Code: Select all
image_id='".$id."'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."'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