2

I have this query:

SELECT count(member_id) FROM member_favorite_business WHERE business_id=3912 AND member_id=413

When I profile this query with my mysql profile tools, it says that this query is very bad. It said:

JOIN SIZE: 16128 (VERY BAD, VERY SLOW)


CONTAINS FULL TABLE SCANS (BAD)

My question is, how can I improve this?

Any suggestions would be greatly appreciated

Cheers!

Improve this question
1
  • Are there indeces on any of the columns? Commented Nov 4, 2010 at 11:44

2 Answers 2

Reset to default
4

Add an index on (business_id, member_id).

You can also write COUNT(*) instead of count(member_id) since member_id cannot be NULL, but I doubt this will make a considerable difference to the performance.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

wow.. what a difference.. it is now VERY GOOD.. thank you so much Mark!
i've got another one: SELECT id FROM business WHERE ( coordinates!='' AND getDistance('-2.1032155,49.1801863', coordinates)<10 ) AND id NOT IN ('6', '4118') ORDER BY rand() LIMIT 0,5 it said: JOIN SIZE: 4418 (BAD) USING TEMPORARY (BAD) USING FILESORT (BAD) Any idea how to optimise this?
0

Your second question in the comment is more difficult, you may want to post it as a seperate question. The ORDER BY rand() will cause problems, maybe others have suggestions about this. Create an index on (coordinates,id) or (id,coordinates) see which is faster. I'm assuming the 2.1032155,49.1801863 are sample coordinates and you want to find 5 matches within 10 "somethings" of the point.

Improve this answer

1 Comment

That is right, i'll re-post this on different Question. Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.