Comparing regex performance between MongoDB, MySQL and PostgreSQL
MongoDB has a very rich regular expression (regex) interface. Regex is available in many of the places that ordinary character strings are accepted. While convenient to use, I had some questions about how MongoDB regex performance stacks up to regex performance in other (SQL) Databases.
MongoDB regexes are simple to use. Instead of using double quotes, use forward slashes to delimit strings to match:
The difference is, double quotes require a full, exact string match. Forward slash (regex) will match rows where the string matches the pattern given. If no special regex characters are given columns that contain the search string anywhere in the column are considered positive matches.
The first query will only find rows that exactly match the IP Address of
The second query will find rows that match that IP address at the beginning of the line
and with any (or none) characters following. The
character is an
which means that it helps the regex engine restrict the search to the beginning of a string.
Notice that MySQL uses
. PostgreSQL uses the
(tilde) character instead of
I tested three different queries for performance on MongoDB, MySQL and PostgreSQL:
|Query||MongoDB Pattern||MySQL/PostgreSQL Pattern|
- bolanchor - Regex Query with a beginning of line anchor - only matches at the beginning of the line
- msiecount - Regex Query to select lines with matching characters in the middle of the line
- bolanchor - Regex Query with a end of line anchor - only matches at the end of the line
|Query||Rows||MongoDB ms||PostgreSQL ms||MySQL ms||Winner|
MongoDB wins on the second execution, once it caches some of the data in memory.
MongoDB doesn’t have a query cache like MySQL, so you can’t get instant results for
the exact same query.
So, MongoDB wins handily in two cases. PostgreSQL makes a mostly respectable showing, but MySQL (MariaDB in this test) brings up a distant third place.