7

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 whether 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)?

6
  • @Bubbles you probably should redesign the database so that you don't need to run an IN clause with a few thousand IDs – Patrick Mar 22 '11 at 5:42
  • How? I need to retrieve records based on a calculated distance and matching tags, sorted by street name – Dexter Mar 22 '11 at 13:51
  • @Bubbles the post table, is that post as in address? If so you could run a query based on postal (zip) code or by city – Patrick Mar 22 '11 at 14:21
  • No the post table was just the example from the Book. I have Store (id, entry-date, etc.), Tags, StoreTags, StoreLocation, StoreGeoCoordinates, StoreDescription – Dexter Mar 22 '11 at 14:34
  • What if you took the results from your second query and get the appropriate post.id from there and use the IN clause in last query? Or is that what you are doing? – Patrick Mar 22 '11 at 14:38
2

I have done this in a few places. Doing multiple simple queries and building an ID list in the application logic, even with the ID list containing 10,000+ ID's made significant performance increases. The table I was querying had around 5 million records and doing a JOIN was painfully slow. After switching to using IN with an ID list it took about 1% of the time the JOIN took.

0
3

breaking up complex queries into simple ones

Poppycock. Why do extra effort when MySQL is quite willing to do it for you? As for performance -- there is probably no difference except that the broken up queries require more round trips to the server.

OTOH, there are cases where you can outsmart the optimizer. But your example was not one of those.

IN (thousands-of-ids) is possible, but painful, for the server. It will sort and de-dup them, then leave them in some kind of structure for repeated binary searching. I have seen lots of such queries, but only those over, say, 50K items raised any eyebrows.

There are times when this rewrite helps:

SELECT ... ORDER BY ... LIMIT ...

-->

SELECT b... 
FROM tbl b 
   JOIN ( SELECT id FROM TBL WHERE ... ORDER BY ... LIMIT ... ) a 
   ON a.id = b.id 

But that is to avoid hauling around extra junk that will be thrown away by the LIMIT.

0

I have done this in a few cases, where it did bring a significant, measurable speed increase. Then again, in other cases this didn't help much. I don't believe there's a universal answer along the lines of "yes, this is always good" or "no, this is always bad"; I posit that "the query optimizer will usually find a better solution than a programmer": so far, I've only found a few corner cases where I had to do the query optimizer's work, such as this.

As with any optimization: check with your specific data, profile the program (not just the query!) and see whether the difference is real or just wishful thinking.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.