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:
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');
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.
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...
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');