I have the following PHP code which gets data from a form, and the MySQL query which takes over 15 minutes to complete. It needs to be much faster. I'm wondering if I am neglecting any best practices for using PHP variables and MySQL queries, and/or if the query structure itself is responsible for the unacceptable length of time it takes to complete.
The basic purpose of the query is to count the number of times the voters PK appears as the votes FK, and select the corresponding record from the voters table if the count is above a predefined number (a percentage based on the calculation done on the final PHP conditional statement, and the part of the query that filters out everything beyond 2004).
The voters table has 1 million rows, while the votes table has 7 million, both CountyEMSID keys are indexed, however I'm not allowed to change the table structures, and data will only ever be read from them, never written to. Any advice or suggestions on how to speed things up will be greatly appreciated!
Also, sorry for the essay :P
/* ========== GET VALUES ========== */
if (isset($_GET["StreetName"])) { $searchStreetName = $_GET["StreetName"]; } else { $searchStreetName = "%"; }
if (isset($_GET["City"])) { $searchCity = $_GET["City"]; } else { $searchCity = "%"; }
if (isset($_GET["Zip"])) { $searchZip = $_GET["Zip"]; } else { $searchZip = "%"; }
if (isset($_GET["DOBY"])) { $searchDOBY = $_GET["DOBY"]; } else { $searchDOBY = "%"; }
if (isset($_GET["DOBM"])) { $searchDOBM = $_GET["DOBM"]; } else { $searchDOBM = "%"; }
if (isset($_GET["DOBD"])) { $searchDOBD = $_GET["DOBD"]; } else { $searchDOBD = "%"; }
if (isset($_GET["Gender"])) { $searchGender = $_GET["Gender"]; } else { $searchGender = "%"; }
if (isset($_GET["Party"])) { $searchParty = $_GET["Party"]; } else { $searchParty = "%"; }
if (isset($_GET["ED"])) { $searchED = $_GET["ED"]; } else { $searchED = "%"; }
if (isset($_GET["AD"])) { $searchAD = $_GET["AD"]; } else { $searchAD = "%"; }
if (isset($_GET["CD"])) { $searchCD = $_GET["CD"]; } else { $searchCD = "%"; }
if (isset($_GET["CO"])) { $searchCO = $_GET["CO"]; } else { $searchCO = "%"; }
if (isset($_GET["SD"])) { $searchSD = $_GET["SD"]; } else { $searchSD = "%"; }
if (isset($_GET["CC"])) { $searchCC = $_GET["CC"]; } else { $searchCC = "%"; }
if (isset($_GET["VoterActivity"])) { $searchVoterActivity = (($_GET["VoterActivity"] / 100) * 18); } else { $searchVoterActivity = "0"; }
/* ========== GET DATA ========== */
$sql = "SELECT voters.*, COUNT(votes.CountyEMSID) AS 'activity'
FROM voters INNER JOIN votes ON voters.CountyEMSID = votes.CountyEMSID
WHERE voters.StreetName LIKE '$searchStreetName%'
AND voters.City LIKE '$searchCity%'
AND voters.Zip LIKE '$searchZip%'
AND voters.DOBY LIKE '%$searchDOBY'
AND voters.DOBM LIKE '%$searchDOBM'
AND voters.DOBD LIKE '%$searchDOBD'
AND voters.Gender LIKE '$searchGender%'
AND voters.Party LIKE '$searchParty%'
AND voters.ED LIKE '%$searchED'
AND voters.AD LIKE '%$searchAD'
AND voters.CD LIKE '%$searchCD'
AND voters.CO LIKE '%$searchCO'
AND voters.SD LIKE '%$searchSD'
AND voters.CC LIKE '%$searchCC'
AND votes.ElectionDateY >= 2004
AND (
votes.ElectionType = 'GE'
OR votes.ElectionType = 'PR'
OR votes.ElectionType = 'PP'
)
GROUP BY votes.CountyEMSID
HAVING COUNT(votes.CountyEMSID) >= '$searchVoterActivity'
ORDER BY voters.LastName, voters.FirstName, voters.DOBY, voters.DOBM, voters.DOBD ASC
LIMIT $start, $limit";
/* ========== CREATE TABLE ========== */
CREATE TABLE voters (
CountyEMSID varchar(9) NOT NULL,
LastName varchar(30) NOT NULL,
FirstName varchar(30) NOT NULL,
MiddleInitial varchar(1) NOT NULL,
NameSuffix varchar(4) NOT NULL,
HouseNumber varchar(10) NOT NULL,
HouseNumberSuffix varchar(10) NOT NULL,
ApartmentNumber varchar(15) NOT NULL,
StreetName varchar(50) NOT NULL,
City varchar(40) NOT NULL,
Zip varchar(5) NOT NULL,
ZipCode4 varchar(4) NOT NULL,
MailingAddress1 varchar(50) NOT NULL,
MailingAddress2 varchar(50) NOT NULL,
MailingAddress3 varchar(50) NOT NULL,
MailingAddress4 varchar(50) NOT NULL,
DOBY varchar(4) NOT NULL,
DOBM varchar(2) NOT NULL,
DOBD varchar(2) NOT NULL,
Gender varchar(1) NOT NULL,
Party varchar(3) NOT NULL,
Other varchar(30) NOT NULL,
ED varchar(3) NOT NULL,
AD varchar(2) NOT NULL,
CD varchar(2) NOT NULL,
CO varchar(2) NOT NULL,
SD varchar(2) NOT NULL,
CC varchar(2) NOT NULL,
RegY varchar(4) NOT NULL,
RegM varchar(2) NOT NULL,
RegD varchar(2) NOT NULL,
Status varchar(2) NOT NULL,
VoterType varchar(1) NOT NULL,
StatusChangeY varchar(4) NOT NULL,
StatusChangeM varchar(2) NOT NULL,
StatusChangeD varchar(2) NOT NULL,
LastVoted varchar(4) NOT NULL,
Telephone varchar(12) NOT NULL,
KEY CountyEMSID (CountyEMSID)
)
/* ========== CREATE TABLE ========== */
CREATE TABLE votes (
CountyEMSID varchar(9) NOT NULL,
County varchar(2) NOT NULL,
AD varchar(2) NOT NULL,
ED varchar(3) NOT NULL,
Party varchar(3) NOT NULL,
ElectionDateY varchar(4) NOT NULL,
ElectionDateM varchar(2) NOT NULL,
ElectionDateD varchar(2) NOT NULL,
ElectionType varchar(2) NOT NULL,
VoterType varchar(1) NOT NULL,
KEY CountyEMSID (CountyEMSID)
)
