Optimizing MySQL Queries for Maximum Speed

Part 3: Optimize MySQL Queries and Indexes

MySQL is a database management system used in most websites and applications. Data engineers rely on SQL (Structured Query Languages) to access and edit data found in MySQL databases.

MySQL queries function as a bridge between code and the massive amount of data that databases store. MySQL database performance tuning enhances data retrieval speed and efficiency as it impacts an application's or website's overall performance. Therefore, optimizing MySQL queries for maximum speed is essential.

• How to Optimize MySQL Queries for Large Datasets
• MySQL Query Optimization Techniques
• Summary

How to Optimize MySQL Queries for Large Datasets

Most systems need to be highly optimized for selects - take a news site that performs millions of daily queries but where the data arrives in large batches of text files. So, inserts need to be optimal for parts of the day without noticeably affecting the millions trying to access the data.

MySQL Slow Query Log

Assuming a nicely formatted '|' delimited text file that we want to insert into a table, take this piece of PHP code:

if (!($fp = fopen("datafile.txt","r"))) {               // open the file for reading
  print "nUnable to open datafile.txt for writing";    // display error
  exit();                                       // end the running of the program
}


while (!feof ($fp)) {                   // loop through the file line by line
  $sline = fgets($fp, 4096);            // put the data into the variable $sline
  $sline = chop($sline);                        // remove the newline
  list($eno,$fname,$sname,$telno,$salary) = split("|",$code);
                        // split the line on "|", populating the ind. variables
  $db->query("insert into employee(employee_number,firstname,surname,
tel_no, salary 
   values($eno,'$fname','$sname','$tel_no', $salary)");
}                                               // end while loop

This would work, but it would be very slow. The index buffer would be flushed after every insert. Until recently, MyISAM tables (MySQL default) did not allow data to be inserted at the same time as being read. The new format does, but only if no deleted records are present (highly unlikely in a heavily used system). So, the entire table is locked for the duration of each insert. This is fine on a low-volume site, but you'll soon notice the backlog when you get hundreds or thousands of queries per second.

How to Speed Up MySQL Queries

Getting Data In

The best way to insert the data is to use MySQL's "LOAD DATA INFILE". This is much faster (20 times according to MySQL).

The syntax is simple, and the code becomes a lot simpler, too:

$db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");

LOAD DATA INFILE has defaults of:

FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY ''

If you don't specify any of these clauses like an ordinary insert, you need to specify a field list if the order of the fields is different. Always specifying a field list is good practice for all queries. If someone adds a field to the table at a later stage, you don't want to go back and have to fix all your previous INSERT and SELECT statements.

If you can't get this to work correctly, look at the format of your text file - every problem seen with LOAD DATA has been caused by a corrupted text file. Every field in every row must be delimited correctly.

You may not always be inserting from a text file - perhaps your application needs to do many unrelated inserts continually. There are ways to make sure the mass of users selecting are not badly affected.

• The first is to use INSERT LOW PRIORITY. This waits until there are no more reads waiting to happen, waiting for the gap, and not pushing in as it were. If your database is a rush hour special, there may never be a gap, and the client performing the INSERT LOW PRIORITY may start to grow cobwebs.
• An alternative here is INSERT DELAYED. The client is immediately freed, and the insert put into a queue (with all the other INSERT DELAYED's still waiting for the queue to end). This means that there can be no meaningful information passed back to the client, (such as the auto_increment value), as the INSERT has not been processed when the client is freed. Also, be aware that a catastrophe such as an unexpected power failure here will result in the queued INSERT's being lost.

For neither of these methods will you know when the data will be inserted, if at all, so use caution.

Getting Data Out

It's not only getting the data in that needs to be quick. Sometimes, you need to get it out quickly, too. (Say you've accidentally loaded yesterday's classified ads, for example). Don't do a:

DELETE FROM classifieds;

Rather, use:

TRUNCATE TABLE classifieds;

The difference here is that DELETE drops records one by one, and that can be 1 million one by one's too slow! Note that this does not apply before version 4.0 of MySQL. At the time of writing, most of you will still be using 3.x versions (if you do a DELETE FROM tablename on a non-empty table and get 0 records back. As a result, you're running an earlier version. To fix this problem, MySQL made DELETE remove records one by one to return the number of records deleted, but TRUNCATE still did the quick delete. Also, earlier versions than 3.23.33 used TRUNCATE tablename, not TRUNCATE TABLE tablename).

MySQL Query Optimization Techniques

Below are other MySQL query optimization guides that you may find helpful:

• Optimizing MySQL Queries with Indexes
• Optimizing MySQL Indexes for Query Calculations

Resource list:

• www.mysql.com (Complete documentation for MySQL)

Summary

Discover tips on how to optimize MySQL queries for large datasets. MySQL queries function as a bridge between code and the massive amount of data that databases store, and optimizing MySQL queries is essential for maximum speed and efficiency. To optimize MySQL queries for large datasets, use MySQL's "LOAD DATA INFILE" to insert data, use INSERT LOW PRIORITY or INSERT DELAYED to insert data during peak usage times, and use TRUNCATE TABLE instead of DELETE FROM to remove data from a table quickly.