Tools Blog Learn Quizzes Smile API Log In / Sign Up
Tools Blog Learn Quizzes Smile API Log In / Sign Up
« Return to the tutorials list
We have updated the website and our policies to make sure your privacy rights and security are respected.
Click here to learn more about the way our website handles your data.

Remove this message.

3 useful MySQL tricks for every web developer

Difficulty: 35 / 50 Tweet
mysql dolphin
In this tutorial you will learn how to use regular expressions to do complex searches in MySQL, a very simple method of debugging performance issues and how you can grant specific permissions to your MySQL users.

REGEXP

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,

  • '<br' - matches all string that contain <br
  • ( )? - matches an optional space - (You don't have top put the space in parenthesis but I added them to make everything clearer)
  • (/)? - matches an optional slash
  • ...and > matches the closing part of the br tag.

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.

Logging Slow Queries in MySQL

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.

GRANT

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.

comments powered by Disqus