Possible optimization for "startsWith" in JDOQL (MySQL implementation)


Andy
 

You can easily enough develop your own handler for "startsWith" method, and provide a benchmark comparison of current versus that. This extension point http://www.datanucleus.org:15080/products/accessplatform_5_1/extensions/extensions.html#rdbms_sql_method


Page bloom
 

I noticed that if a JDOQL query filter has something like:

firstNameLow.startsWith("fred")

it gets translated to a LOCATE in the where clause

where LOCATE('fred',a0.first_name_low) = 1;

In MySQL (tested on version 5.6) and likely other RDBMSes a LOCATE will not take advantage of any index on the column.

Indexes can only be used when the wildcard appears at the end of the 'like' string - and this is the exact case for a 'starts with' operation.

So, if the where clause generated for a 'startsWith' used a 'like' with a trailing % then the query, in my dataset, is measured as 0.00 seconds instead of the LOCATE version which takes multiple seconds to complete.

where first_name_low like 'fred%'

I can achieve the above using the "matches"  method:

firstNameLow.matches("fred.*")

but I thought the startsWith would seem more intuitive and convenient than having to resort to matches with regex - plus it's not obvious that a startsWith generates a LOCATE and not a 'like' until you look under the hood.