I've got a large table (10m rows approx, all fake test data at the moment.).
id user and action are both not unique, but a user can perform an action only once. (Meaning there will only ever be one combo of id_user and id_action.
x and y are both only going to be an INT of 1 - 100
I've indexed every column.
CREATE TABLE IF NOT EXISTS `test` (
`id_user` int(11) NOT NULL,
`id_action` int(11) NOT NULL,
`x` int(11) NOT NULL,
`y` int(11) NOT NULL,
KEY `x` (`x`),
KEY `y` (`y`),
KEY `id_user` (`id_user`),
KEY `id_action` (`id_action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
It's for a game I'm trying to build.
What I want to do is search the table for any other users that have similar scores of x, within a range dictated by y.
For example, if user 1 does action 1 with a score of 75 in x and has a range of 10 in y. I want to show all other users who scored from 65-85 (as the range is 10 set in y).
This is what I've got and I just ran it on my local laptop.. it timed out after 300 seconds... :(
SELECT * FROM test
WHERE
id_user != 1 AND
x BETWEEN
((SELECT x from test WHERE id_action = 1 AND id_user = 1) - (100 - (SELECT y FROM test WHERE id_action = 1 And id_user = 1)))
AND
((SELECT x from test WHERE id_action = 1 AND id_user = 1) + (100 - (SELECT y FROM test WHERE id_action = 1 And id_user = 1)));
This search on the 10,000,000 rows I have (100 actions with 100,000 test users.. all random numbers) just fails.
I'm going to investigate joining the table on to itself now, but I thought a sub-select would be more efficient. I'm learning as I go here... any advice would be appreciated... :)
