It's All in the Indexes
Today I launched a new feature that I'd been thinking about for a few weeks and writing for a few days. It is an extra side window when viewing the chess board that can show you all games so far on the site that had the position as this game currently has. It shows every next move that was made in previous games and how many games that move occured in. You can then click a hyperlink to see a list of those games and then click anyone of those links to view the game. It really works quite well, and on my mini local site was wonderfully quick.
In preparation for this I'd done some major work on the moves database (where all chess moves made are stored) to give every row a move number. This is simply the number of the move as it occured in the game, so black's 2nd move is actually move 4, and so on. This all sounds straight forward but there 90000 moves in the table and they all needed this number determining. I managed this with some cunning PHP and a very patient wife who ran the script a few times for me (several hundred times because it did the move number allocations in small chunks so as not to kill the site). I also created a few extra indexes (indicies?) on the moves table so that finding moves would work fast. There was an index on the column that links to the move's game, one for the coordinates and also one for the move number column. Splendid.
I uploaded all of the necessary files and eagerly loaded an online game to view my handy work. Hmmmmm, the page is loading slowly. Hmmmm, it's still loading. {5 minutes later} Errrmm, it's still loading, my connection is definately live, and no one else I know can get in. Uh oh.
My error was two-fold. First, I hadn't put a limit on the number of moves to match a game to so it was trying to match 30, 40, 100 moves in a game. MySQL gets a bit annoyed at you when you try to join more than 31 tables so it threw a hissy fit. Also, when matching just under 30 moves it was still taking an age to run. Now, if you know anything about chess you'll know that there are more possible chess positions than there are sub-atomic particles in the universe (I'm only counting protons and neutrons, not quarks, neutrinos and other stuff). After just 10 moves each the game is usually diverse enough to be completely unique, and even after 5 moves each finding a match is pretty rare. So, I told the code to only match on the first 10 game moves (5 moves per player).
Still slow.
Now I did say that my error was two-fold, so here's the second:
I was always told that indexes can make a database query fast. Well today I learnt that there are different types of index and some are faster than others. It turns out that if I index the game_id field and the move number field as a combined index a standard query was sped up 40 times, and a combined index on the coordinates also worked wonders. So, by just tweaking the database structure I managed to get a several hundred times speed increase without any change to my code. Cool!
What Else Will It Do?
That's a good question. I plan to have it show which were the most successful moves, i.e. what the game win percent was when each move was made. It will also list named openings and tell you the next move to make for that opening. I also plan to overcome the 10 moves limit (just in case) - I have a cunning plan for that!
New Icons
One of the more subtle requirements for this moves database thing was the need for another link in the chess board side window. That's not a problem, except that there was no room without making it double lined. The solution? Replace the text links for each window with image links. Ruthie to the rescue - she drew some little GIF images for me and we uploaded them. So far they've had a reasonable response, and I think they look great! There's still room for one more side window icon... (that was a joke, there are no plans yet...)
In preparation for this I'd done some major work on the moves database (where all chess moves made are stored) to give every row a move number. This is simply the number of the move as it occured in the game, so black's 2nd move is actually move 4, and so on. This all sounds straight forward but there 90000 moves in the table and they all needed this number determining. I managed this with some cunning PHP and a very patient wife who ran the script a few times for me (several hundred times because it did the move number allocations in small chunks so as not to kill the site). I also created a few extra indexes (indicies?) on the moves table so that finding moves would work fast. There was an index on the column that links to the move's game, one for the coordinates and also one for the move number column. Splendid.
I uploaded all of the necessary files and eagerly loaded an online game to view my handy work. Hmmmmm, the page is loading slowly. Hmmmm, it's still loading. {5 minutes later} Errrmm, it's still loading, my connection is definately live, and no one else I know can get in. Uh oh.
My error was two-fold. First, I hadn't put a limit on the number of moves to match a game to so it was trying to match 30, 40, 100 moves in a game. MySQL gets a bit annoyed at you when you try to join more than 31 tables so it threw a hissy fit. Also, when matching just under 30 moves it was still taking an age to run. Now, if you know anything about chess you'll know that there are more possible chess positions than there are sub-atomic particles in the universe (I'm only counting protons and neutrons, not quarks, neutrinos and other stuff). After just 10 moves each the game is usually diverse enough to be completely unique, and even after 5 moves each finding a match is pretty rare. So, I told the code to only match on the first 10 game moves (5 moves per player).
Still slow.
Now I did say that my error was two-fold, so here's the second:
I was always told that indexes can make a database query fast. Well today I learnt that there are different types of index and some are faster than others. It turns out that if I index the game_id field and the move number field as a combined index a standard query was sped up 40 times, and a combined index on the coordinates also worked wonders. So, by just tweaking the database structure I managed to get a several hundred times speed increase without any change to my code. Cool!
What Else Will It Do?
That's a good question. I plan to have it show which were the most successful moves, i.e. what the game win percent was when each move was made. It will also list named openings and tell you the next move to make for that opening. I also plan to overcome the 10 moves limit (just in case) - I have a cunning plan for that!
New Icons
One of the more subtle requirements for this moves database thing was the need for another link in the chess board side window. That's not a problem, except that there was no room without making it double lined. The solution? Replace the text links for each window with image links. Ruthie to the rescue - she drew some little GIF images for me and we uploaded them. So far they've had a reasonable response, and I think they look great! There's still room for one more side window icon... (that was a joke, there are no plans yet...)
posted by Stephen Robson @ 8:56 pm
0 Comments:
Post a Comment
<< Home