Valhalla Legends Forums Archive | Web Development | Select statement optimization

AuthorMessageTime
SecretShop
This is not necessarily web development based but I figure this forum is the most applicable.  Im running MySQL 5.0 currently as a backend database on my battle.net community management software.  Im trying to optimize my queries for when a user joins and leaves as these are being called, in peak hours atleast, very often.  Heres what I have currently and im wondering if using MySQL's UNION command is the most efficient way to run this kind of query:
[code]
select flags, flags_mask, commands, commands_mask from users where username='blah'
    union select flags, flags_mask, commands, commands_mask from clients where client='WAR3'
    union select flags, flags_mask, commands, commands_mask from wildcards where 'blah' like pattern
    union select flags, flags_mask, commands, commands_mask from groups where groupname in
    (select groupname from users_groups where username='blah');
[/code]

The username of the user in this example is "blah" and the client the user is using is warcraft III.

my desired result is a table of columns flags, flags_mask, commands, commands_mask that has a variable ammount of rows depending on how these queries match.  In my code I am just using the binary OR operatior to mix these togather into a single result.
April 1, 2006, 8:58 PM
rabbit
That is the best way I can think of doing it.  But, (yes, a but!) since I am completely OCD, I have "fixed" the code..it was bugging me...

[code]SELECT (`flags`, `flags_mask`, `commands`, `commands_mask`) FROM `users` WHERE `username` = 'blah'
    UNION SELECT (`flags`, `flags_mask`, `commands`, `commands_mask`) FROM `clients` WHERE `client` = 'WAR3'
    UNION SELECT (`flags`, `flags_mask`, `commands`, `commands_mask`) FROM `wildcards` WHERE 'blah' LIKE 'pattern'
    UNION SELECT (`flags`, `flags_mask`, `commands`, `commands_mask`) FROM `groups` WHERE `groupname` IN
    (SELECT `groupname` FROM `users_groups` WHERE `username`= 'blah');[/code]
April 1, 2006, 9:29 PM

Search