If you checked out the benchmark page above, you will notice that MySQL is many times faster than the competition on almost everything. Why is this so? Some of the speed comes from things that are not supported in MySQL, such as transactions, foreign keys and triggers.
Because the MySQL server is coded mainly by one person with many years of coding experience, very little redundant code is in it. Most of the basic algorithms also come from an era of slow CPUs and small amounts of memory. The algorithms have mostly been extended to use larger caches if there is available memory.
As a result, MySQL has a compact fast design (the code size of the server is less than 1MB on an i386) which normally uses very little memory, but can be configured to take advantage of large amounts of memory.
MySQL has many useful optimizations for maximum speed. Some examples are:
Most joins are done in one sweep.
Very few normal joins require temporary tables. Joins involving a GROUP BY and an ORDER BY on something other than the GROUP BY part will create a (usually memory-based) temporary table.
Memory-based temporary tables have dynamic hashing.
MySQL has a user configurable key cache and a record cache to quickly scan tables. Open tables are cached in a table cache.
An index optimizer quickly finds which possible index to use with a specific WHERE clause.
A range optimizer finds the range for an index which will then be used to find the matching rows. The range optimizer can optimize queries that use a combination of >=, >, =, <=, < and LIKE (of type “keyword%”) on a key column. When many possible indexes can be used, the range optimizer will choose the index that matches the smallest number of rows.
A join optimizer rearranges the tables in a SELECT statement in the optimal order. In the rare case the MySQL join optimizer gets this wrong, one can force the optimizer to join the tables in a particular order with the STRAIGHT_JOIN keyword.
For each sub-join, a simple WHERE is constructed to get a fast WHERE evaluation for each sub-join and to skip records as soon as possible.
A WHERE optimizer removes constant conditions.
Early detection finds invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.
SQL functions are implemented through a highly optimized class library. Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items, and the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings by using malloc and free.
In queries of type
SELECT ... WHERE col IN (a,b,c,d,e,f,...)
the IN part is sorted and checked through binary searching.
MIN/MAX on an indexed column is done with one key fetch.The MIN/MAX optimizer can also find the best value when one has specified all preceding index columns in the WHERE condition:
SELECT MAX(index_part2) FROM tbl_name WHERE index_part_1=constant;
COUNT(*) on a single table without a WHERE is retrieved directly from the table information.
If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree rather than the data file for greater speed.
Indexes are used to find rows matching
WHERE index_column LIKE "prefix%".
GROUP BY and DISTINCT are optimized by creating a temporary HEAP table with the GROUP BY (or whole row) as a unique index.
INSERT DELAYED inserts rows in a queue to the SQL server, which is useful if you are using MySQL for logging and can't afford to wait until the INSERT is completed.
Index compression is used to get smaller and faster indexes.
One of the design decisions that has resulted in the greatest number of questions is the lack of transactions. There are, of course, applications that require transactions to work, but a wide range of applications work very well without them.
Some people believe that since MySQL does not support transactions, it cannot support many simultaneous users. Each MySQL client gets a dedicated thread in the MySQL server, which allows different users to access the same tables at the same time. All MySQL operations are atomic: no other users can change the result for a running query.
When designing MySQL, we had a greater need for speed than for transactions. It's no use having transactions if the SQL server becomes so slow it's unusable for what you need to get done.
Another common misunderstanding is that transactions provide extra robustness through the redo logs. This extra security can be had by simple means in MySQL. That is, do normal backups and then apply the update log to the backup. The update log is a file containing all SQL statements that change any data.
MySQL also has external utility programs to check, optimize and repair individual tables.
|Designing Electronics with Linux||May 22, 2013|
|Dynamic DNS—an Object Lesson in Problem Solving||May 21, 2013|
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
- New Products
- Linux Systems Administrator
- Senior Perl Developer
- Technical Support Rep
- UX Designer
- Designing Electronics with Linux
- Dynamic DNS—an Object Lesson in Problem Solving
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Using Salt Stack and Vagrant for Drupal Development
- Nice article, thanks for the
7 hours 59 min ago
- I once had a better way I
13 hours 45 min ago
- Not only you I too assumed
14 hours 2 min ago
- another very interesting
15 hours 55 min ago
- Reply to comment | Linux Journal
17 hours 49 min ago
- Reply to comment | Linux Journal
1 day 43 min ago
- Reply to comment | Linux Journal
1 day 59 min ago
- Favorite (and easily brute-forced) pw's
1 day 2 hours ago
- Have you tried Boxen? It's a
1 day 8 hours ago
- seo services in india
1 day 13 hours ago
Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- 5-21-13, Prototyping Pi Plate Kit: Philip Kirby
- Next winner announced on 5-27-13!
Free Webinar: Hadoop
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers
Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.
Some of key questions to be discussed are:
- What is the “typical” Hadoop cluster and what should be installed on the different machine types?
- Why should you consider the typical workload patterns when making your hardware decisions?
- Are all microservers created equal for Hadoop deployments?
- How do I plan for expansion if I require more compute, memory, storage or networking?