When you're doing searches, retrieving results using of MySQL regular expressions can prove to be very a handy tool.
Let's say you want to get a list of articles that contain <br> tags, but they were written inconsistently. To solve this, you can do a SELECT using LIKE, for every possible variation you can think of (Ex: SELECT * FROM x WHERE column LIKE '%<br>%' or LIKE '%<br />%' or LIKE '%<br/>% ...').
With regular expressions, however, it's much easier:
SELECT * FROM `table_name` WHERE `column_name` REGEXP '<br( )?(/)?>;
Let's go through the above query. The REGEXP operator can be seen as similar to the LIKE operator with the difference that it uses regular expressions to search for a pattern. Actually there is also an alias for "REGEXP" in MySQL, called "RLIKE" which basically means LIKE pattern matching with regular expressions.
Looking at the actual pattern,
This means that our query will return all rows that contain either <br> or <br > or <br/> or <br /> ... More about regular expressions in MySQL here.
Sometimes queries are just painfully slow and they affect user experience when browsing your website. The most common causes for performance issues in MySQL are missing indexes on joined columns or wrong data types.
Let's imagine the following table structure:
students table: | studentid | name | -------------------- | 1 | Mike | | 2 | John | | 3 | Jeff | | 4 | Anne | classes table: | classid | classname ------------------------- | 1 | Math | 2 | Programming | 3 | Biology classes_to_students table: | studentid | classid ------------------------- | 1 | 1 | 2 | 2 | 3 | 2 | 4 | 3
To get the list of students enrolled in a particular class you would execute this query:
SELECT s.name FROM students as s INNER JOIN classes_to_students as cs ON s.studentid = cs.studentid INNER JOIN classes as c ON c.classid = cs.classid WHERE c.classname='Programming';
By doing some simple testing with more than 50.000 entries on each of the above tables, you'll notice that if the 'studentid' and 'classid' columns are not indexed and don't have the same data type as their counterparts in the students and classes tables the query can take more than one second to execute.
But what happens if the table structure is much more complicated and not that easy to debug or if you're not the one who designed the database structure and queries? - Easy! You log slow queries.
long_query_time = 1 slow_query_log =1 slow_query_log_file = /yourpath/slowquery.log
To log slow queries go to your my.cnf file on the server and add the above two lines. long_query_time defines how much should a query execution take to be considered slow, while 'slow_query_log_file' defines the path where the slow queries should be logged.
If you are using a MySQL version that's older than 5.2, the variable name is log_slow_queries, but this was deprecated since 5.1.29 and is removed in MySQL version 5.6.
It is a good security practice to define different users for database administration tasks and database usage tasks respectively. When creating the MySQL user for your website, the one you use in your code to execute INSERT, UPDATE, DELETE and SELECT statements, you should consider not allowing that user to execute queries like ALTER, TRUNCATE, DROP and so on.
The main reason for doing so is because if you have a security flaw in your code, a user with limited privileges will do less damage to your data. To grant a user specific permissions execute the queries below:
CREATE USER 'mysuer'@'myhost' IDENTIFIED BY 'mypass'; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'mysuer'@'myhost';
That's it, check back soon for more MySQL tutorials.