I've just figured out why a query being run from my PHP front end was taking about 50 seconds longer than it does when I run it from MySQL monitor, but would like to understand the implications better. I'm using two variables, $extension and $today and sending them to mysql via parameterization. When I choose to refer to $extension as an integer, it takes a very long time. When I refer to it as a string, it's pretty instantaneous.
Query;
$stmt = $db_conn->prepare(
"select from_unixtime(dateTimeOrigination) as date_of_call_origination ,
callingPartyNumber as calling_party_number,
originalcalledpartynumber as original_called_party_number,
finalCalledPartyNumber as final_called_party_number,
SEC_TO_TIME(duration) as duration,
origDeviceName, destDeviceName
from cdr_records
where (callingPartyNumber= ? or originalcalledpartynumber= ? or finalcalledpartynumber = ?)
and
from_unixtime(dateTimeOrigination) between ? and ADDDATE(?, INTERVAL 1 DAY) order by datetimeorigination desc");
My bind statements;
Original
$stmt->bind_param('iiiss', $extension, $extension, $extension, $today, $today);
Changed to
$stmt->bind_param('sssss', $extension, $extension, $extension, $today, $today);
So, I have two questions;
1 - $extension is technically a string but it's always composed of numbers. Isn't it better to call it an integer? It can be any length between 5 and 15 characters.
2 - By refering to it as a string, could it potentially open itself to any security issues?
I'm surprised it makes much difference. Although your tests appear to contradict this view, I would think that the slow part of the query is this bit:
FROM_UNIXTIME(dateTimeOrigination) BETWEEN ? and ADDDATE(?, INTERVAL 1 DAY)
I would change that to
datetimeorigination BETWEEN UNIX_TIMESTAMP(?) AND UNIX_TIMESTAMP(? + INTERVAL 1 DAY)