Is the inbuilt % wildcard faster at doing that sort of thing, or is it generally faster to use a regex? I can't find any info on it but maybe I'm not looking in the right place... maybe I'm not even asking the right question...

I don't think you're asking the right question. A better question for most people writing code is whether yourself (or anyone else) can read it in 6 months time. Most people aren't familiar with regexp so it fails on this score. Obviously some queries can only be written in regexp in which case you use them.

Speed wise it probably depends more on whether the string you're comparing against has a wild card at the front or not - if it does then it's unlikely to be matched against an index and so will be slow. If you're having performance issues then it should only take a few moments to try both queries against your data to see which is fastest.

they are, for all intents and purposes, equally fast

why? because they both operate in memory

and how did the value that they operate on get into memory? it was retrieved from the database table

and both of them are equally slow if an index cannot be used

if "field" is the only column being filtered by the WHERE clause, then it comes down to whether the index on that column, if one even exists, can be used

it ~can~ be used if the LIKE % wildcard is not in the leftmost position in the search string

i'm not sure whether the index can be used in the REGEXP if the search string begins with ^

you should test that

I don't think MySQL will look at an index on a field if you're comparing it with a regexp. I think this is fair enough as a regexp by default will match any part of a string so is unlikely to be useful. If you do have something like '^a' then theoretically you could use the index but in practise you'd be more likely to use like 'a%' anyway. Simple enough to test though.

it never ceases to amaze me that people ask questions such as this, especially on somethign as reltively trivial as this
why not learn by doing rather than asking the question and waiting for 3 hours 21 minutes before getting an indeterminate answer


or is it just me?

Nope - I guess our time is cheaper than his

I think this post deserves an answer, as it's something that I've often wondered myself when I'm answering questions on other forums, in subjects where I'm more knowledgeable than I am here.

Firstly, "learning by doing" implies a knowledge of how to set up the required testing environment. Specifically: how is someone who doesn't even know if regex is faster than wildcard, supposed to know (a) what constitutes a reliable measure of "faster", and (b) whether or not their test could have been influenced by factors they weren't aware of?

Secondly, it is not always the case that a simple "is A faster than B" question requires, or seeks, a correspondingly simple answer. In many cases the true value of a question is not in acquiring a specific item of knowledge, but in broadening an understanding of the subject by listening to experts discuss it.

I would also suggest that your point about the time elapsed is moot. Since I didn't preface my question with "URGENT! please help!!1!1!", you can assume that I'm not going to be sitting here banging away at F5 in the meantime, and that I'm quite happy to wait hours or days on the chance that someone with a useful insight into the subject will wander along.

In this particular case, I've learnt that I wasn't asking the right question, and that indexes are far more important when it comes to query speed. Maybe that's self-evident to you; maybe it's something I should have known already. In any case, I consider my ability to effectively use mySQL has improved through reading the replies that you appear to consider a waste of your time. I sincerely hope that I can convince you otherwise, and that you continue to answer questions like these, even if just with "you're not asking the right question".

Finally, I'd ask you to consider the length of time taken to compose this reply and weigh that against any conception you may have of my being lazy, relying on others to do work for me, or simply being ungrateful for the advice that is freely given here.

When writing any code your first thought should be whether it works. Next, and nearlly as importantly, you should consider whether anyone else looking at the code in 6 months time will be able to understand it. Lastly, and only if the code is causing performance issues, should consider tuning the code to make it run faster.

It would have been easy for you to compare the performance of rlike and like - my system just shows the time to run a query at the bottom of the screen. So I'd just run the two bits of SQL on your data and see what the times are. Sure there are various factors that might affect the performance but at the end of the day if the speed seems roughly the same then does it really matter?

Your question is certainly valid and I'm sure you're not lazy but sadly WE are lazy and we often respond to many queries each day so we always like to see a little effort put in by the OP - hence we occasionally have a bit of fun with our responses