MYSQL/SQL: Query optimisation

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!

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.

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.