$sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1";
$rs = mysqli_query($conn, $sQueryUpdateTime);
$row = mysqli_fetch_assoc($rs);
the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows";
// takes 30 ms. on the indexed int
$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows";
// takes 300 ms. due to "PatientName" been a text field, even as I did index it
So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
..tho, I find that any 2nd argument in the ORDER BY is just slow.
Is there a way to query this in a way to have a faster result back ?
Thanks
PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE; and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).