note 118951 added to mysqli-stmt.bind-param

Parameter type matters more than you might think! A cautionary tail to any who would seek to find less than thorough solutions for automating prepared statements. Take the following mysqli_stmt extension method for example. <?php public function param_type($param) { if (ctype_digit((string) $param) return $param <= PHP_INT_MAX ? 'i' : 's'; if (is_numeric($param)) return 'd'; return 's'; } ?> At face value this seems like a perfectly straightforward and innocuous function. Something like this served as a small piece in a larger automation extension which dutifully served its purpose efficiently handling hundreds of thousands of queries a day. Now I know what you're thinking: it doesn't handle blob types. Well we didn't work with blob types (and still don't) so that's never been an issue. This problem was far more insidious and ultimately pernicious than that. So what went wrong? The problem began to surface when we started automating SELECT queries on a newly created index for a column designed to store telephone numbers. The column was of type VARCHAR but the data stored was always formatted as an integer. Not a problem when performing a write operation but as soon as we went to read from the table on this index everything went to hell. We're not entirely sure, but as near as we can tell the act of binding a parameter to a VARCHAR index as 'i' instead of 's' on a read query is detrimental in the following way: MySQL will ignore the b-tree on the index and perform a full table scan. With smaller tables this may never present itself as a significant performance issue. When your tables get into the tens of millions of rows, however... ---- Server IP: 72.52.91.14 Probable Submitter: 2605:a601:440:ba01:2d3f:4ca0:3e9b:8308 ---- Manual Page -- http://php.net/manual/en/mysqli-stmt.bind-param.php Edit -- https://master.php.net/note/edit/118951 Del: integrated -- https://master.php.net/note/delete/118951/integrated Del: useless -- https://master.php.net/note/delete/118951/useless Del: bad code -- https://master.php.net/note/delete/118951/bad+code Del: spam -- https://master.php.net/note/delete/118951/spam Del: non-english -- https://master.php.net/note/delete/118951/non-english Del: in docs -- https://master.php.net/note/delete/118951/in+docs Del: other reasons-- https://master.php.net/note/delete/118951 Reject -- https://master.php.net/note/reject/118951 Search -- https://master.php.net/manage/user-notes.php