I have 3 tables...school, wrk_key, and county. School table has about 4000+ records (info on elementary, middle, private, high, etc.). Wrk_key table has about 122,000+ records and contains scores for Grade 11 students (only high schools and excludes elementary, middle). County table contains about 100+ records and only has 2 columns (county_id, county). Queries on database are taking way too long (30 seconds+). Using MySql and PHP.
In the code I supplied below, you will notice that I am taking in a posted variable from another page, which would be the first letter of the school name. It is being assigned to $schalpha, which is being used to query the database. I have tested on 2 different servers (Windows 2003w/IIS and Unix w/Apache) and same speed.
$schalpha = $_POST['schlalpha'];
$schalpha_look = "select wrk_key.school_id, wrk_key.district_id, wrk_key.county_id, school.fac_name, school.address, school.city from wrk_key left join school on (wrk_key.school_id = school.school_id and wrk_key.district_id = school.district_id and wrk_key.county_id = school.county_id) where school.fac_name like '".$schalpha."%' group by school.fac_name asc";
$schalpha_res = mysql_query($schalpha_look);
echo "
<h1>School Search Results for Schools Starting with the Letter " . $schalpha .".</h1>
<table border='0' width='90%' cellpadding='0' cellspacing='1' align='center'>
<tr>
<td class='wkey2'>School Name</td><td class='wkey2'>Address</td><td class='wkey2'>City</td></tr>";
while ($row = mysql_fetch_array($schalpha_res))
echo "<tr><td class='datawkey'><a href='wkey_overall.php?schoolID=" . $row['county_id'] . $row['district_id'] . $row['school_id'] ."'>".$row['fac_name'] ."</a></td><td class='datawkey'>" . $row['address'] . "</td><td class='datawkey'>" . $row['city'] . "</td></tr>";
echo "
</table>
