Help Optimizing MySQL Table (~ 500,000 records).
- by Pyrite
I have a MySQL table that collects player data from various game servers (Urban Terror). The bot that collects the data runs 24/7, and currently the table is up to about 475,000+ records. Because of this, querying this table from PHP has become quite slow. I wonder what I can do on the database side of things to make it as optomized as possible, then I can focus on the application to query the database. The table is as follows:
CREATE TABLE IF NOT EXISTS `people` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL,
`ip` int(4) unsigned NOT NULL,
`guid` varchar(32) NOT NULL,
`server` int(4) unsigned NOT NULL,
`date` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Person` (`name`,`ip`,`guid`),
KEY `server` (`server`),
KEY `date` (`date`),
KEY `PlayerName` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='People that Play on Servers' AUTO_INCREMENT=475843 ;
I'm storying the IPv4 (ip and server) as 4 byte integers, and using the MySQL functions NTOA(), etc to encode and decode, I heard that this way is faster, rather than varchar(15).
The guid is a md5sum, 32 char hex. Date is stored as unix timestamp.
I have a unique key on name, ip and guid, as to avoid duplicates of the same player.
Do I have my keys setup right? Is the way I'm storing data efficient?
Here is the code to query this table. You search for a name, ip, or guid, and it grabs the results of the query and cross references other records that match the name, ip, or guid from the results of the first query, and does it for each field. This is kind of hard to explain. But basically, if I search for one player by name, I'll see every other name he has used, every IP he has used and every GUID he has used.
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Search: <input type="text" name="query" id="query" /><input type="submit" name="btnSubmit" value="Submit" />
</form>
<?php if (!empty($_POST['query'])) { ?>
<table cellspacing="1" id="1up_people" class="tablesorter" width="300">
<thead>
<tr>
<th>ID</th>
<th>Player Name</th>
<th>Player IP</th>
<th>Player GUID</th>
<th>Server</th>
<th>Date</th>
</tr>
</thead>
<tbody>
<?php
function super_unique($array)
{
$result = array_map("unserialize", array_unique(array_map("serialize", $array)));
foreach ($result as $key => $value)
{
if ( is_array($value) )
{
$result[$key] = super_unique($value);
}
}
return $result;
}
if (!empty($_POST['query'])) {
$query = trim($_POST['query']);
$count = 0;
$people = array();
$link = mysql_connect('localhost', 'mysqluser', 'yea right!');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("1up");
$sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (name LIKE \"%$query%\" OR INET_NTOA(ip) LIKE \"%$query%\" OR guid LIKE \"%$query%\")";
$result = mysql_query($sql, $link);
if (!$result) {
die(mysql_error());
}
// Now take the initial results and parse each column into its own array
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$name = htmlspecialchars($row[1]);
$people[] = array(
'id' => $row[0],
'name' => $name,
'ip' => $row[2],
'guid' => $row[3],
'server' => $row[4],
'date' => $row[5]
);
}
// now for each name, ip, guid in results, find additonal records
$people2 = array();
foreach ($people AS $person) {
$ip = $person['ip'];
$sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (ip = \"$ip\")";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$name = htmlspecialchars($row[1]);
$people2[] = array(
'id' => $row[0],
'name' => $name,
'ip' => $row[2],
'guid' => $row[3],
'server' => $row[4],
'date' => $row[5]
);
}
}
$people3 = array();
foreach ($people AS $person) {
$guid = $person['guid'];
$sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (guid = \"$guid\")";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$name = htmlspecialchars($row[1]);
$people3[] = array(
'id' => $row[0],
'name' => $name,
'ip' => $row[2],
'guid' => $row[3],
'server' => $row[4],
'date' => $row[5]
);
}
}
$people4 = array();
foreach ($people AS $person) {
$name = $person['name'];
$sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (name = \"$name\")";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$name = htmlspecialchars($row[1]);
$people4[] = array(
'id' => $row[0],
'name' => $name,
'ip' => $row[2],
'guid' => $row[3],
'server' => $row[4],
'date' => $row[5]
);
}
}
// Combine people and people2 into just people
$people = array_merge($people, $people2);
$people = array_merge($people, $people3);
$people = array_merge($people, $people4);
$people = super_unique($people);
foreach ($people AS $person) {
$date = ($person['date']) ? date("M d, Y", $person['date']) : 'Before 8/1/10';
echo "<tr>\n";
echo "<td>".$person['id']."</td>";
echo "<td>".$person['name']."</td>";
echo "<td>".$person['ip']."</td>";
echo "<td>".$person['guid']."</td>";
echo "<td>".$person['server']."</td>";
echo "<td>".$date."</td>";
echo "</tr>\n";
$count++;
}
// Find Total Records
//$result = mysql_query("SELECT id FROM 1up_people", $link);
//$total = mysql_num_rows($result);
mysql_close($link);
}
?>
</tbody>
</table>
<p>
<?php
echo $count." Records Found for \"".$_POST['query']."\" out of $total";
?>
</p>
<?php
}
$time_stop = microtime(true);
print("Done (ran for ".round($time_stop-$time_start)." seconds).");
?>
Any help at all is appreciated!
Thank you.
