How to improve execution time for this SQL query?

jdbrown444 0
Viewed 42

I have a table with the following example:

ID name task startDate endDate
1 John Day 2022-09-20 2022-09-21
2 Joe Midday 2022-09-20 2022-09-21
3 John Day 2022-09-22 2022-09-23
4 Sara Night 2022-09-20 2022-09-21
5 Joe Night 2022-09-24 2022-09-25

I would like to count the rows that have a name in a given name list AND task in a given task list AND within a date range.

I am using php and the code and sql query I am currently using is:

$taskList = list of all the tasks I want to search for

$nameList = list of all the names I want to search for

SELECT COUNT(ID) FROM table WHERE
`task` IN ('".implode("', '", $taskList)."')
AND `name` IN ('".implode("', '", $nameList)."')
AND (startDate >= '".$startDate."')
AND (startDate <= '".$endDate."')

It takes ~40 milliseconds per execution but I need to do this multiple times. Is there a way I can shorten the execution time?

execution performance sql php
1 Answers

Looks like your query is fast enough, but on some case it also slow. Please make sure:

  1. Add index group to your table columns that where query executed: task, name, startDate

  2. Consider don't use asterisk to select count query with the column name that not nullable values, try to use select count(primary_key_column)

Just Test The difference.

But, before start please doing escape the values before doing direct query to sql to prevent sql injection and or invalid data value.

/**
 * @var PDO $pdo
 */
$taskList  = array_map([$pdo, 'quote'], $taskList);
$nameList  = array_map([$pdo, 'quote'], $nameList);
$startDate = $pdo->quote($startDate);
$endDate = $pdo->quote($endDate);
$quotedTaskList = implode(',', $taskList);
$quotedNameList = implode(',', $nameList);

$query = "SELECT COUNT(primary_key_or_non_nullable_column) FROM table WHERE
`task` IN ({$quotedTaskList}')
AND `name` IN ({$quotedNameList})
AND (`startDate` >= {$startDate})
AND (`startDate` <= {$endDate})
";
ArrayIterator0
Related