Posts Tagged ‘mysql’
- In: databases | SQL
- 4 Comments
We’re planning on upgrading from an archaic mysql engine (3.58) to the latest, shiny release version (5.1), so I’ve undertaking a code review of some of our administration applications to see what this unearthed.
Turns out the password hashing for the MySQL native PASSWORD() function changed in version 4.1, so our login system won’t work in 5.1. Great.
Turns out we’ll be able to skirt around this by making sdure this line is in the new my.cnf file:
old-passwords = 1
It’s great this was thought of, as PASSWORD() is a one-way, irreversible function, so authentication will fail for any old application using it when it is run on mysql >= 4.1.
thanks to [ducea.com]
————
Need to improve your MySQL skills? I recommend:
High Performance MySQL: Optimization, Backups, Replication, and More
- In: databases | SQL
- 13 Comments
MySQL’s REGEXP function is handy when you need to search for specific text strings within a textual column, but over large record sets, it’s performance can be less than fantastic.
Recently I had a situation at work where I needed to do a first name / last name search across a single name field. Due to poor database design, the name field was in the form “lastname, first names”. Initially I used REGEXP to come up with something like this:
Select table1.some-data, table2.some-other-data
from
table1, table2
where
table1.name REGEXP "^[ a-z]{0,},[ a-z]{0,}'.$firstName.'[ a-z]{0,}$"
and
table1.name REGEXP "^[ a-z]{0,}'.$lastName.'[ a-z]{0,},[ a-z]{0,}$"
and
table1.key = table2.foreignKey
Our testing showed that this query produced a good quality result for name searches, but I had a gut feeling it would slow the database server considerably when under a bit of load.
Testing using JMeter confirmed this hunch and I was faced with the task of optimising the query without affecting the quality of the results.
What to do? What would give the pattern-matching punch of REGEXP without killing the server?
Enter the LIKE function. I’ve used LIKE before but didn’t realise how powerful (and fast) it was. After a bit of tweaking I cam up with the following:
Select table1.some-data, table2.some-other-data
from
table1, table2
where
table1.name LIKE "%,%'.$firstName.'%"
and
table1.name LIKE "%'.$lastName.'%,%"
and
table1.key = table2.foreignKey
I had (falsely) thought initially that LIKE‘s wild card characters (the “%” in the query) could only occur at the start or end of a query, but it turns out they can be used for all sorts of pattern matching goodness. The query using LIKE turned out the same results as the query using REGEXP, only it was at least 10 times faster.
The downside with LIKE‘s pattern matching (which didn’t affect my query) is that it’s wild card will match any character, whereas REGEXP can be tuned using normal regular expressions to providfe much more granular query control.
If you just need fast, ‘any character’ pattern matching though, LIKE is your friend.
recent comments