Player Search - Improved
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.
Tags: update