3

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's level and connection. However I want to order this so that a user's level is the most important and then it will look at the connection. So if there are two users with the same level, it will look at the connection instead. Maybe there will be more columns in future that can be used to further refine the search. I have seen that on single column selects like this, I could use abs() however I'm not sure if this is possible on this as well?

create table users (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  connection INT(11),
  level INT (11)
);

create table lobby (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT(11),
  CONSTRAINT user_id FOREIGN KEY (id)
  REFERENCES users(id)
);

insert into users values (null,5,6);
insert into users values (null,7,10);
insert into users values (null,10,9);
insert into users values (null,10,4);
insert into users values (null,1,8);

insert into lobby values (null,1);
insert into lobby values (null,2);
insert into lobby values (null,3);
insert into lobby values (null,4);
insert into lobby values (null,5);

So if I'm using user_id = 1 as my user I would have the following select statement:

select lobby.id, lobby.user_id, users.connection, users.level
from lobby INNER JOIN users
ON lobby.user_id = users.id 
where user_id <> 1 and 
users.connection between 0 and 10 and
users.level between 1 and 11
order by abs(connection-5);

This select statement comes back with the following table:

| id | user_id | connection | level |
|----|---------|------------|-------|
|  2 |       2 |          7 |    10 |
|  5 |       5 |          1 |     8 |
|  3 |       3 |         10 |     9 |
|  4 |       4 |         10 |     4 |

However what I want is the following output:

| id | user_id | connection | level |
|----|---------|------------|-------|
|  2 |       2 |          7 |    10 |
|  5 |       5 |          1 |     8 |
|  4 |       4 |         10 |     4 |
|  3 |       3 |         10 |     9 |

Thanks.

EDIT: Ok so I am currently using the abs function and it's working well, however it is working on multiple fields and because of this I feel that the statement is not very efficient. Is there a better way of doing this?

2

Simply add another column, order by abs(connection-5), abs(level-4) is valid syntax:

select lobby.id, lobby.user_id,
   users.connection, users.level
from lobby INNER JOIN users
ON lobby.user_id = users.id
where lobby.user_id <> 1
  and users.connection between 0 and 10 
  and users.level between 1 and 11
order by abs(connection-5), abs(level-4);
  • Thanks dnoeth, this works as expected, however it does run quite slow and this is only with three records in my table, dyou know of any other way that is more efficient? – kai246 Jan 23 '16 at 16:10
  • 1
    Slow with three records? What indexes exist? You will need indexes on the join columns. – dnoeth Jan 23 '16 at 16:13
  • Well the user_id on the lobby table is a foreign key of the primary key (id) on the users table, so these two are indexed. I assume this is what you meant by needing indexes on the join columns? – kai246 Jan 23 '16 at 16:52
  • @kai246: Well, it's MySQL, you never know if user_id is actually indexed :-) See SHOW INDEX. Did you check if the indexes are actually used? – dnoeth Jan 23 '16 at 18:19
  • Yeah I just checked this now, and it seems that they all being indexed fine. I managed to reduce the time down now as I only really need one record from the lobby table so I just put a limit of 1 to the end of the SQL statement. I dont know if that should actually help or not but it seems to be doing the trick at least. – kai246 Jan 23 '16 at 18:39
0

Try something like this:

SELECT l.id
      ,l.user_id # current user ID
      ,others.id # other user's ID
      ,others.connection
      ,others.level
FROM lobby l
INNER JOIN users AS myuser
  ON l.user_id = myuser.id
INNER JOIN users AS others
  # Remove l.user_id = others.id
  ON myuser.id <> others.id
  AND ABS(myuser.connection - others.connection) <= 5
  AND ABS(myuser.level - others.level) <= 5
WHERE myuser.id = 1
order by ABS(myuser.connection - others.connection),
        ABS(myuser.level - others.level);
  • Hi, thanks for the answer. This is pretty much what I was discussing with dnoeth above. Its working as expected however is there a more efficient way of doing this? – kai246 Jan 23 '16 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.