I noticed that if a JDOQL query filter has something like:
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:
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.