mysql queires and UNION??

[grant.hayman]

I have an advertsers database which baisicly holds banner code and some parameters to distinguish placement and usergroups etc.

I have have 4 ad slots which very in size, shape and how many banners it will hold etc.

So to call the banner code i need 4 queries, one for each slot. Which means 4 db hits.

If i write the query as a UNION query and order the result as i need will this be a lot better for vbulletin for overhead etc or since it is doing 4 selects anyway does it really make much difference.

Baisicly do UNION queries help with vbulletin overhead is it classed as 1 hit to the db instead of 4.

Thanks

Grant

[MoT3rror]

If you have your ads in one table in your database, you should be able to just do one query.

[grant.hayman]

hi MoT3rror,

I can't see a way of doing this in 1 query as the 4 selects are getting diffrent type of ads, size and position plus each select will be getting random results.

from what I can see it would definetly need 4 selects but was wondering if union them all would be better for vb so that all the results will come back in 1 result set.

it would be easier to do 4 diffrent selects from a coding point of view but I am thinking it might be a bit heavy on vb to do 4 extra selects.

simple example:

Select bannercode from table where "where clause"
ORDER BY RAND() LIMIT 1;
UNION
Select bannercode from table where "another where clause"
ORDER BY RAND() LIMIT 2;
UNION
Select bannercode from table where "another where clause"
ORDER BY RAND() LIMIT 1;
UNION
Select bannercode from table where "another where clause"
ORDER BY RAND() LIMIT 1;

Grant

----------------------------------------

After playin with this it seems it is impossible to union and rand() all the selects so it looks like im going to have to do 4 selects anyway :(

[MoT3rror]

Have you try doing a limit of 4 on your query like this?
SELECT bannercode FROM table WHERE ... ORDER BY RAND() LIMIT 4

[grant.hayman]

Have you try doing a limit of 4 on your query like this?
SELECT bannercode FROM table WHERE ... ORDER BY RAND() LIMIT 4


But that would get 4 results per select I thought....

I will give it a go.

[Amenadiel]

I believe that using union will cause even more database load than making the queries on a separate basis. This is because the union instruction does an implicit distinct which in this case you just don't need.

You could use "union all" instead but, anyway, this would end up using the same database resources than the 4 queries separately.