0

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?

jdbrown444
  • 9
  • 1
  • 1
    What indexes do you have on the above table? Edit your question and add the output from: `SHOW INDEXES FROM YOUR_TABLE;`. Also, why would you have to do this multiple times? Why not do it once and save the result for later use? And `~40 ms` does not seem like much? – Cyclonecode Sep 20 '22 at 19:02
  • your code looks very **vulnerable** to **sql injection** you should take a look at https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk Sep 20 '22 at 19:02
  • For a clearer meaning I would also use `BETWEEN` in the above e.g. `AND (startDate BETWEEN '" . $startDate . '" AND '" . $endDate . '")`, not like it is going to increase the actual performance. – Cyclonecode Sep 20 '22 at 19:07
  • This has obviously got PHP involved, so the least you should do is show the query in situ within a piece of PHP – RiggsFolly Sep 20 '22 at 19:10
  • How many is multiple times? And what changes in the query to require multiple executions – RiggsFolly Sep 20 '22 at 19:15
  • Sorry, I do have an index column in the actual table. I also use prepared statement to prevent sql injection. – jdbrown444 Sep 20 '22 at 19:21
  • Another quesiton would be "Why" multiple times? could it be a paramaterized query which gathers all the data on one trip unions results and sends back? – xQbert Sep 20 '22 at 19:21
  • It is executed multiple times because each execution uses a different combination of Names and Tasks. The different executions are used downstream to make tables and graphs. – jdbrown444 Sep 20 '22 at 19:23
  • It usual takes a total of 30 seconds to complete. – jdbrown444 Sep 20 '22 at 19:24

1 Answers1

0

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})
";
ArrayIterator
  • 54
  • 1
  • 3
  • Thanks ArrayIterator. I have tried searching for just the primary key (ID) but it did not improve the speed. I have edited my question to reflect this. – jdbrown444 Sep 20 '22 at 19:29