CODEWITHSUNDEEP
sqlmysqloptimization
gumpi
gumpi

Reputation: 281

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!

Upvotes: 2

Views: 120

Answers (2)

Jaydee
Jaydee

Reputation: 4158

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.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 839144

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.

Upvotes: 4

Related Questions