Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 02-20-2008, 03:15 PM
grant.hayman grant.hayman is offline
 
Join Date: Feb 2007
Posts: 32
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default mysql queires and UNION??

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
Reply With Quote
  #2  
Old 02-20-2008, 10:24 PM
MoT3rror MoT3rror is offline
 
Join Date: Mar 2007
Posts: 423
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

If you have your ads in one table in your database, you should be able to just do one query.
Reply With Quote
  #3  
Old 02-21-2008, 06:59 AM
grant.hayman grant.hayman is offline
 
Join Date: Feb 2007
Posts: 32
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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:

Code:
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
Reply With Quote
  #4  
Old 02-21-2008, 12:14 PM
MoT3rror MoT3rror is offline
 
Join Date: Mar 2007
Posts: 423
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Have you try doing a limit of 4 on your query like this?
[SQL]SELECT bannercode FROM table WHERE ... ORDER BY RAND() LIMIT 4[/SQL]
Reply With Quote
  #5  
Old 02-21-2008, 01:49 PM
grant.hayman grant.hayman is offline
 
Join Date: Feb 2007
Posts: 32
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by MoT3rror View Post
Have you try doing a limit of 4 on your query like this?
[SQL]SELECT bannercode FROM table WHERE ... ORDER BY RAND() LIMIT 4[/SQL]

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

I will give it a go.
Reply With Quote
  #6  
Old 02-25-2008, 02:08 AM
Amenadiel's Avatar
Amenadiel Amenadiel is offline
 
Join Date: Sep 2006
Posts: 171
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 01:21 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.07234 seconds
  • Memory Usage 2,206KB
  • Queries Executed 11 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (6)post_thanks_box
  • (6)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (6)post_thanks_postbit_info
  • (6)postbit
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete