Need to perform some logic based on the existence of a record in db. Since this function will be called quite often, I'm wondering if the following can be made more efficient...
Also note that the table is an association table(employee_id and department_id together form a composite primary key), so there will only ever be one record that meets the criteria.
Ex:
<?php
function checkEmpDptAssoc($employee_id, $department_id)
{
$sqlQuery = sprintf("SELECT COUNT(*)
FROM employee_department ed
WHERE ed.employee_id = '%d'
AND ed.department_id = '%d'",
$employee_id,
$department_id);
$result = mysql_query($sqlQuery);
$row = mysql_fetch_assoc($result);
if ((int) $row['COUNT(*)'] > 0) {
return true;
} else {
return false;
}
}
?>
EXISTS might be a little quicker since you only need to ensure one exists.
SELECT 1 FROM DUAL
WHERE EXISTS (
SELECT *
FROM employee_department ed
WHERE ed.employee_id = '%d'
AND ed.department_id = '%d'
)
Maybe you can try:
"SELECT ed.employee_id
FROM employee_department ed
WHERE ed.employee_id = '%d'
AND ed.department_id = '%d'
LIMIT 1"
Since you just need to check if a single record exists, then perhaps:
if (!empty($row)) {
return TRUE;
} else {
return FALSE;
}
[edit: based on @ColShrapnel's comment, which I had sadly forgotten]
# instead of above if...else condition:
return empty($row);
Sorry, I don't have the time to test this right now, but what I can say is that if you want the performance to improve (putting aside count() vs limit), start with seeing if you can add an index for a column or both columns in your where clause, being employee_id and department_id.. If employee_id is supposed to be unique, make it a unique index for example:
-- non unique
create index idx_employeeid on table(employee_department)
-- unique
create unique index idx_employeeid_unq on table(employee_department)
A perfect example of what I am always talking about
A question, asked absolutely out of nowhere.
A couple of answers, regarding EVERYTHING beside the REALLY important things.
And a poor alone comment, the only sensible answer to the question.
The only thing you really have to be concerned of, is a proper indexing.
The rest of your question, all these syntax issues, various variants to run THE SAME query are all NOTHING.
the only improvement I can think of is just getting rid of unnecessary code.
select 1 instead of count(*) and then you can make it just
return (bool)mysql_fetch_assoc($result);
it won't affect performance of the code but can affect performance of the coder a bit.