0

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones:

Converting

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

To

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

And sort of doing the actual join yourself in your application.

My Question is wether this is stil such a good idea when the final query has a where-clause with a few thousand IDs it needs to match (the actual table itself has about 500k entries).

What I mean is, will there be a big penalty for having a query like

SELECT * FROM post WHERE post.id in (123,456,567, ... <a few thousand IDs here> ... ,9098,8904);

instead of the join-statement above? Would it help to move this logic to Stored Procedures inside the Database (while considering how poorly stored procedures are implemented in MySQL)?

Dexter
  • 3,072
  • 5
  • 31
  • 32
  • See the following link for a performance comparison between a large IN clause and a temporary join table. http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/ – a'r Mar 22 '11 at 00:37
  • thanks, that page was helpful – Dexter Mar 22 '11 at 00:48

1 Answers1

2

Join decomposition is useful in certain situations, but in most situations the joins are going to be faster.

In your case, I would stick with the joins instead of passing in a few thousand IDs in an IN clause.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109