Generating more efficiant SQL

[PassiveSmoking]

When you want to fetch data from a SQL database it's common practice to do something like the following:



function getRecord ($recordID)
{
$query = 'SELECT * FROM myTable WHERE recordID = '.intval ($recordID).'; ';
if ($result = mysql_query ($query))
{
while ($row = mysql_fetch_assoc ($result))
{
$return [] = $row;
}
}
return ($return);
}


So what happens if you need more than one record at once?


for ($thisRec = 1; $thisRec <= 10; $thisRec++)
{
$records [] = getRecord ($thisRec);
}


So what's wrong with this? Well strictly speaking there's nothing wrong with it. but it's not an optimal solution. Most database systems are optimized to return datasets spanning multiple rows, and they have query planners that analyse a query and decide how best to execute it. For these reasons fetching 10 records with 10 queries is not an optimal solution. If possible you want to fetch them with a single query. So how would we go about doing this?


function getRecordList ($recIDList)
{
if (is_array ($recIDList))
{
foreach ($recIDList as $thisRec)
{
if (!$recQueryList)
$recQueryList = 'WHERE )';
else
$recQueryList .= ' OR ';
$recQueryList .= 'recordID = '.intval ($thisRec);
}
if ($recQueryList)
{
$recQueryList .= ')';
$query = 'SELECT * FROM myTable '.$recQueryList.'; ';
if ($result = mysql_query ($query))
{
while ($row = mysql_fetch_assoc ($result))
{
$return [] = $row;
}
}
}
}
return ($return);
}


It's a bit more work to program but the payoff is you can now fetch an arbitrary number of records in a single query, which will put less strain on your database engine. I've used MySQL in this example but the same should hold true for most other major databases.

If you find yourself fetching single records on a regular basis and find creating an array every time annoying then simply write a wrapper function.


function getRecord ($recordID)
{
return (getRecordList (array ($recordID)));
}


so you can easily fetch simple records with getRecord but also have getRecordList when you need to do a bulk select.

[eak]

In MySQL you can just do something like this, which makes your function kind of inefficient.

SELECT * FROM myTable where recordID in (1,2,3,4,5,6,7,8,9,10);