If you're using that query with large tables, you should have indexes on at least:
- Anything you're joining on (e.g. Foreign keys which don't currenly have indexes on; all primary keys will have automatic indexes)
- Anything you're ordering by (e.g. score)
I cannot begin to explain how much difference having the right indexes makes on larger data sets. If for example, you have 10k+ rows, the difference can be dramatic- You could see queries going from taking maybe 100s of milliseconds to just a few ms
Mark
[web] Optimizing SQL Highscore
You should read konForce's posts again. He's got many good points.
Very true. I'd simply use an auto-incrementing integer as level identifier. When the user enters a level, you must fetch some data anyway, getting one more integer costs almost nothing here.
However, you can make your highscore table fixed-size by removing the text/varchar column, which is good, and despite indices, looking up an integer is probably still by order of magnitude faster.
Quote:1. Assign a unique value instead of a string for the level-name. Could have gotten away with just hashing the name.
Very true. I'd simply use an auto-incrementing integer as level identifier. When the user enters a level, you must fetch some data anyway, getting one more integer costs almost nothing here.
However, you can make your highscore table fixed-size by removing the text/varchar column, which is good, and despite indices, looking up an integer is probably still by order of magnitude faster.
1. Assign a unique value instead of a string for the level-name. Could have gotten away with just hashing the name.Quote:2. Avoid the join; it's a waste of energy.
What's it good for anyway, if only the top ten is wanted, and you know the user's ID anyway :)
Never fetch data that you already know (btw. do you absolutely need "location"?).Quote:Original post by __ODIN__This can also be done using a stored procedure (or, with some hack, using a trigger... stored proc is better).
Since we only really care about the top 10 scores, I ended up setting up a cron-job to execute across each unique level
Your dataset will remain constant (10 elements), and the query string has already run through the parser and the query optimizer has already done its work, too (re-runs every time otherwise).
Lastly,you save one client connection and the associated overhead.
You are using a BIGINT as your userid, thats a wast of memory, a MEDIUMINT UNSIGNED offers enough room for 16 million entries.
Looking at your tables I guess you have a 1-on-1 relation ship between GameUser and GameScore, so you could insert an entry with scores=0 as soon as a player is registered into gamescore
Apply a PRIMARY KEY to userid in both tables(somehow primary keys seem to be faster than plain indices on large tables).
I had a table with a x y z coordinates as indices and wanted to do an interval search on them
the table had 262144=512*512 entries, (x,y) coordinates where the map coordinates, z was the continent
A simple search
//select a 8x8 region from the map
SELECT * FROM province WHERE z==zsearch AND x>=minx AND x <= maxx AND y >=miny AND y <=maxy LIMIT 64;
this query took 1.12 seconds
Now since each of my maps are 512*512 so they are always 262144 entries I removed the z column, got rid of the primary key(provinceid) auto_increment and mapped the x,y,z values onto the primary key(provinceid) at insertion time,
Since all my primary keys are static and predefined I modified the select clause to this
//inside a stored procedure
SELECT * FROM province WHERE provinceid >= get_provinceid(minx,miny,z) AND provinceid <= get_provinceid(maxx,maxy,z) x>=minx AND x <= maxx AND y >=miny AND y <=maxy LIMIT 64;
this query took only 0.00-0.01 seconds.
I still have to test why the indices performed so pure.
Looking at your tables I guess you have a 1-on-1 relation ship between GameUser and GameScore, so you could insert an entry with scores=0 as soon as a player is registered into gamescore
Apply a PRIMARY KEY to userid in both tables(somehow primary keys seem to be faster than plain indices on large tables).
I had a table with a x y z coordinates as indices and wanted to do an interval search on them
the table had 262144=512*512 entries, (x,y) coordinates where the map coordinates, z was the continent
A simple search
//select a 8x8 region from the map
SELECT * FROM province WHERE z==zsearch AND x>=minx AND x <= maxx AND y >=miny AND y <=maxy LIMIT 64;
this query took 1.12 seconds
Now since each of my maps are 512*512 so they are always 262144 entries I removed the z column, got rid of the primary key(provinceid) auto_increment and mapped the x,y,z values onto the primary key(provinceid) at insertion time,
Since all my primary keys are static and predefined I modified the select clause to this
//inside a stored procedure
SELECT * FROM province WHERE provinceid >= get_provinceid(minx,miny,z) AND provinceid <= get_provinceid(maxx,maxy,z) x>=minx AND x <= maxx AND y >=miny AND y <=maxy LIMIT 64;
this query took only 0.00-0.01 seconds.
I still have to test why the indices performed so pure.
I just wanted to say, it is nice to see good advice being shared here on the boards, totally focused on working together to help people as opposed to bashing each others ideas. The community needs more positive posts like this. Anyways, keep up the good work, and Ill stop hijacking this thread.
Doing some research I probably found the problem, MYSQL seems to use only one index per query, that one which results in the least rows, I could use a multi column index (x,y,z), but I have to try this tonight, so don t take my granted on that
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
