MariaDB Rejects SELECT Syntax

[JamesCobban]

I am trying to resolve a performance issue with a query. In the existing implementation I issue:

SELECT * FROM tblSX WHERE `idsr`=97 AND `type`=2 AND `srcdetail` REGEXP '^1882-0*15870($|[^0-9])' ORDER BY `Order`

As the regular expression indicates I am looking for a field that starts with '1882-' which is followed by the number 15870 which may be padded on the right by 0s but must not be followed by another digit.

EXPLAIN lets me know that this forces a linear search of 115,548 records because the only relevant index is on `idsr`. This takes typically 1.3 seconds which is unacceptably long. I added an index for `type` but this still left 35,243 entries to search linearly and took 350ms. If this query returns no matches the code issues a second query that differs only in that `type` is 30. EXPLAIN tells me that there are 115,548 records that match that combination, and the query takes 1.2 sec. (I tried adding an index for the `srcdetail `field, but that didn't help. Regular expressions are almost impossible to optimize. So I changed the code to try optimizing the logic for the special case of a regular expression that starts with a constant string:

SELECT * FROM tblSX WHERE `idsr`=97 AND `type`=2 AND (LEFT(`srcdetail`,4)='1882' AND `srcdetail` REGEXP '^1882-0*15870($|[^0-9])') ORDER BY `Order`

The server should be able to optimize the LEFT if the field `srcdetail` is indexed. When I issue that command manually it works, but of course my application internally uses PDO execute. So it actually issues:

$stmt = $conn->prepare('SELECT * FROM tblSX WHERE `idsr`=? AND `type`=? AND (LEFT(`srcdetail`,4)=? AND `srcdetail` REGEXP ?) ORDER BY `Order`');
$stmt->execute(array( '97', '30', '1882', '^1882-0*15870($|[^0-9])' );

but MariaDB rejects this with:

error=Array (42000, 1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REGEXP '^1882-0*15870($|[^0-9])') ORDER BY `Order`' at line 1' )

The above is directly from the diagnostic. I do not understand why the command works when I enter it as a string, but not if I use prepare/execute.

[Altrea]

=> https://stackoverflow.com/a/15061783

[JamesCobban]

Thank you for trying to help me. I am afraid that I do not see the relevance of that old post to my issue. I am of course still trying to figure out what is happening. I changed the logic for creating the command template. This did not change what the string looked like when displayed in the diagnostics, but somehow it made a difference to the server. So the commands now work.