How to optimize such sql query?

Question

PHP

Folks, is it possible to optimize this query?

$sql = $this -> db -> pdo -> query("
(SELECT n.title as title,n.npc_id as id FROM npc as n, game as g WHERE g.id = n.npc_id AND g.type = 'npc' AND g.loc = '".$user['loc']."') 
UNION 
(SELECT u.login as title,u.user_id as id FROM `users` as `u`,`game` as `g` WHERE g.id = u.user_id AND g.type = 'user' AND g.loc = '".$user['loc']."' AND u.user_id != '".$user['user_id']."')
"smile -> fetchAll();

I need to add one more union to this, but it seems to me that it can create a rather large load) Or maybe split the requests and then join via array_merge?

Answer 1

No, array_merge is worse. It's one thing - one request but big, or three different requests, but less.
That is, in the second case, the script, on the contrary, will create even more load.
Although you have certain problems with the architecture of the program, if UNION was needed.

Answer 2

It does not seem, but maybe it will. You have two CROSS JOIN queries, both using the same table.
the first request would be prettier

SELECT * FROM NPC AS N
JOIN game AS g ON g.id = n.npc_id
WHERE g.type = 'npc' AND g.loc = '".$user['loc']."'

Similarly, you can update the second. And if you say that you want to get it at all, then it can turn out and merge into one.
UPD:

select COALESCE(n.title,u.login) as title, COALESCE(n.npc_id, u.user_id) as id from game as g
LEFT JOIN npc as n ON g.id = n.id AND g.type = 'npc'
LEFT JOIN users as u ON g.id = u.id AND g.type = 'user'

Try here such request on base to execute. If it works correctly, it will be possible to do filtering.

Answer 3

In general, I am writing a small online toy)
There is an npc table (in-game characters), also a users (users) table, there is also an intermediate game table (it stores locations, and the id of these npc and users, the fields loc, id, type-user or npc)
You need to bring out everyone who is in the location

Answer 4

i think something like this should be:
SELECT u.login AS title
,u.user_id AS id
,n.title AS npc.title
,n.npc_id AS npc.id
FROM users AS u
INNER JOIN game AS g
ON u .id = u.user_id
INNER JOIN npc AS n
ON g.id = n.npc_id
AND g.type = 'npc'
WHERE g.type = 'user'
AND g.loc = '".$user['loc'] ."'
AND u.user_id!= '".$user['USER_ID']."'