Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
@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
2  
"Join decomposition" is a very fancy way of saying "write the bits of the query optimizer we couldn't be bothered with yourself" (!) –  Gaius Mar 23 '11 at 13:27

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
+1 for thinking outside of the box !!!! Sometimes overengineering SQL can be burdensome for very large datasets. So, you went with your gut instincts and found a better way. Good show !!! –  RolandoMySQLDBA Jun 24 '11 at 20:54

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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