Optimizing this query

[Chain Reaction]

Ok I have worked on this for weeks now and I can't figure it out. I'm pretty sure the index isn't working because the two fields are of different type but could someone give advice on what they would do?

I have this query that will be ran every 30 seconds via Ajax and its doing full table scans.

SELECT players.*,
GROUP_CONCAT(gameinfo.playerinfo ORDER BY gameinfo.submittime ASC) AS parray,
GROUP_CONCAT(gameinfo.submittime ORDER BY gameinfo.submittime ASC) AS times,
GROUP_CONCAT(gameinfo.userids ORDER BY gameinfo.submittime ASC) AS uids
FROM game_players AS players, game_gameinfo AS gameinfo
WHERE (gameinfo.gid IN(players.last5) OR gameinfo.gid = players.ingame)
AND players.lastactivity > '0'
GROUP BY players.userid;

Explain returns this:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE players range lastactivity lastactivity 4 NULL 3 Using where; Using temporary; Using filesort
1 SIMPLE gameinfo ALL PRIMARY NULL NULL NULL 2 Using where

I have an index on players.lastactivity and its using it as you can see, but its doing a full scan of the gameinfo table which will soon have thousands of entries in it. gameinfo.gid is the primary key of the gameinfo table and it obviously has an index. I've tried force index on the PRIMARY key but it doesn't use it. I assume its not because players.last5 is a VARCHAR (100) field and gid is a INT (10) field.

The players.last5 field is a field of 5 gameids seperated by commas so it can be used in the IN call there. Can I get some advice on how I should proceed? I don't want this query bringing my server down.

[bmcgee]

An index isn't going to work in the IN() clause. Same as using "like".

You could use a temporary table and break up your last5 field into multiple records and then go against that table.

[physics]

The players.last5 field is a field of 5 gameids seperated by commas so it can be used in the IN call there.

As auto mechanics say "There's your problem". With databases it's rarely a good idea to put comma separated values in a field. What about making a new table called "last5" which simply has entries for the last 5 active players of a given game (based on the game id). That way you can make the field an int 10 and rewrite the query in an optimized way.

[physics]

Oh, and by the way Welcome to WebmasterWorld.com [pubcon.com] Chain_Reaction!

[Chain Reaction]

As auto mechanics say "There's your problem". With databases it's rarely a good idea to put comma separated values in a field. What about making a new table called "last5" which simply has entries for the last 5 active players of a given game (based on the game id). That way you can make the field an int 10 and rewrite the query in an optimized way.

Yeah I have this habit of going with serialized and comma seperated values in fields. I have several projects I have used the comma separated method for. I recall a couple months ago I got tired of my site lagging so I went and looked at slow queries and realised it was one I wrote that was doing a full table scan of a user table with 20,000 users in it. :o I must get out of that phase! :P Problem is i've already coded it all. Ah well, i'll work on redoing it. I'll split it all into another table and go from there. You guys have been helpful, thank you. And thanks for the warm welcome too! :)