4fite.com Blog

Wow, nearly 700 members and 86,000 player records - you guys rock. Sorry that I haven’t been around to make the site better.

Regulars to the site may have noticed not much changing recently. I took a bit of a break from stuff in general - ‘Two Months Off’, so to speak - and now I’m back into full time work. For now, I have to travel about 1.5 hours each way for this job, so it leaves me with much less time to do serious development work on my own stuff, but at least this time it’s a PHP job. So at some point it may motivate me into continuing to iron out this site and work on some of the new things I had planned.

Feel free to keep using the site, even if it’s in a bit of a feature freeze. I know it’s getting a bit slow again, I’ll look into the database resource usage and maybe go ahead with purchasing more from the host.

p.s. for anyone who is a spamusement fan, I occasionally make my own comics too - spam.whoah.net - hope you get a kick out of it

Update: The upgrade has been completed to a point where the site can be open again. There may be a few funny numbers here and there since the scale of ‘played’ time changed from fractional hours to whole seconds (ie. 1.1 hours becomes 3960 seconds or so) messing with a few calculations, but I’ll find them and fix them over the next few days if they’re there.

I’ve been working on changes to the update process to move it from HTML scraping to accessing the available XML data from Steam, which should make it faster and give it more accurate play time numbers. This requires simultaneous changes to the code in the background updater, the front-end presentation and some of the data structures.

To keep it simple, I’ll pull the site down completely sometime this week to make the transition. Probably a weekday afternoon (Sydney time) later in the week.

Not much to say. I’ve been busy with work and personal stuff so I haven’t had a chance to do anything new. Apart from a few hosting glitches, the site is still operating as usual. Historical data seems to be working fine.

The site is slowing down a little, but I don’t have the time to properly test it. For all I know it’s the host’s server configuration. As stated in previous posts, their mysql server does act a little odd.

XML data for the player profile page is now available from the Steam site, but like the testing part above, I don’t have the time to make use of it yet.

That’s all for now. More important news to follow next month.

I’ve written a search that runs independently of the database. I can’t pin it down exactly, it’s either the hosting company’s servers or MySQL itself (or even PHP’s interface to MySQL), but a ” LIKE ‘%whatever%’ ” sometimes draws out to 60+ seconds when checking approximately 40,000 records.

All I know, for now, is that my flat-file index of player names is searched in under 1 second. Not fast by any means, or optimised at all, but this is opposed to a search for the same term direct on the database using phpMyAdmin resulting in a 112 second wait.

My indexes are fine. The same query on my Windows desktop’s MySQL server runs under 300ms, with a database exported from 4fite.com and imported untouched.

According to the MySQL documentation, any LIKE comparison where the argument starts with a wildcard character “does not use indexes”, so that could be the killer, but when I run the same query on my development server there’s no sign of any troubles.

As they are intended to index structured sentences with real words, using FULL TEXT solutions was out of the question. Player names certainly do not fit that profile, and a simply substring search is all that’s needed.

Unfortunately I’m fairly new to debugging a MySQL process in a remote / shared / high volume environment, so I’ll have to do some digging around to find some answers. For now though, my workaround will suffice.