Mysql Activity Union Query

views:

answers:

Mysql Activity Union Query

Hi guys, right to business. I have an activity feed which gets all different kinds of activity from different parts of my site sorts them all out by means of using UNION and an ORDER BY and then a LIMIT to get the top 25 and then displays the activity.

My fellow programmer says that we will run into problems when we have more rows (currently we have 800) and it's fine.

So the question. Will the UNION cause slow down later down the line? If so should we a) Try and put the activity into a new table and then query that. b) Try some sort of view? - (if so could anyone explain how I'm not too sure how!) c) Other...

Thanks for your help. Richard

This is a tricky one as it depends on a lot of variables, such as how busy your tables are etc.

I would imagine the union would slow you down later, as the method you are using is basically doing a union on entire tables so these need to be read into memory before the ordering and the limiting of the number of rows is applied. Your query will get slower as the amount of data increases.

If all the data in the tables is important then the best you can do it try and ensure you index the tables as best you can so at least your ordering runs fast etc. IF some of the data in the tables gets old or stale and you aren't too interested in it, then you might have scope to read just the rows you need into a temp table. This can then be ordered etc.

Ciaran Archer 2010-06-26 11:57:19

Yeah I thought the UNION might slow the thing down in time.I'm beginning to think that perhaps if this is recent history then just limit like the last month that would be the easiest solution.Thank by the way for your inputs allows for a good sounding board.

Richard Housham 2010-06-28 08:34:08

Absolutely, remember the less data the faster it will be. If you can do it without older data then I'd say from a performance POV that's the way to go.

Ciaran Archer 2010-06-28 09:06:21

Why not just limit each of the individual queries to 25 as well? That way you could restrict the amount of rows that come back before being unioned and limited for the final list

Rob Cooney 2010-06-27 07:28:39

Yeah did think of that but then you have a problem with if they are on the next page.

Richard Housham 2010-06-28 08:25:57

You won't get the full picture this way when you sort after the union.

Ciaran Archer 2010-06-28 09:04:29

This is a good idea, gives you the least amount of rows makes it easier for mysql but if you want a big log then look below.Richard

Richard Housham 2010-07-09 10:16:33

I think the best way might be to do a combination of the 2 so

a)yes Index

b) then do the LIMIT 25 on each of the sub queries.

c) Do a where added_date >= date on each of the queries so that we have the correct date order mmm but then that presents problems as to what dates to go for. So if we go to page x which dates do I get.

This is turning into a problem and quite a big one. The size of data we have is going to be quite big.

Thanks for your help. Richard

Richard Housham 2010-06-28 08:31:58

Decided just to make a log and do it that way. Thanks as always for your help. Richard

Richard Housham 2010-07-09 10:15:17

ansaurus

tags:

views:

answers:

Mysql Activity Union Query

related questions