10 tips for optimizing mysql queries
By Justin Silverton
- use the explain command
Use multiple-row INSERT statements to store many rows with one SQL statement.The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.
Example of usage: explain select * from table
explanation of row output:
- table—The name of the table.
- type—The join type, of which there are several.
- possible_keys—This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.
- key—The key actually used in this query, or NULL if no index was used.
- key_len—The length of the key used, if any.
- ref—Any columns used with the key to retrieve a result.
- rows—The number of rows MySQL must examine to execute the query.
- extra—Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).
- use less complex permissions
- specific mysql functions can be tested using the built-in “benchmark” command
If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute
- optimize where clauses
- Remove unnecessary parentheses
- COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
- If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table
- Run optimize table
- avoid variable-length column types when necessary
- insert delayed
- use statement priorities
- Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
- Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.
- use multiple-row inserts
- synchronize data-types
The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.
This command defragments a table after you have deleted a lot of rows from it.
For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.
Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.
Use multiple-row INSERT statements to store many rows with one SQL statement.
Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.
47 Comments so far
Leave a reply
[…] If you’re interested in learning more, I highly recommend Stephane Faroult’s book The Art of SQL.read more | digg story Save This Page […]
[…] The High Performance PHP Blog has posted a nice little article on 10 tips to improve your MySQL queries. There are some great ones there. A sneak peak: 1. Use the explain command 2. Use less complex permissions 3. Specific mysql functions can be tested using the built-in “benchmark” command 4. Optimize where clauses 5. Run optimize table 6. Avoid variable-length column types when necessary 7. Use Insert delayed where you can 8. Use statement priorities 9. Use multiple-row inserts 10. Use multiple-row INSERT statements to store many rows with one SQL statement. 11. Synchronize data-types […]
Hey Justin,
Thanks for the list its quite good. I also found a video on Performance Tuning Best Practices for MySQL for those that are interested (and couldnt be other reading and would rather watch someting!)
http://blog.sherifmansour.com/?p=72
Its quite good and gives you some practical examples
Thank you - I’ll give a few of those a try and see how much the improve performance.
[…] read more | digg story […]
[…] It was on digg.com earlier today, but it’s worth repeating: 10 tips for optimizing MySQL queries. Posted by Karl Katzke on 09Apr07 under Web Dev […]
To toot my own horn a bit, I’ve created a script which looks at a number of server options and status variables and makes recommendations for tuning
Please check it out:
http://forge.mysql.com/projects/view.php?id=44
Optimize your indexes, and optimize your joins (which requires optimized indexes), and you’re gold. Seriously. “EXPLAIN” is your friend - use it, and your app will fly.
A very interesting article, often SQL optimization is not a priority … but it would be
[…] Link Original: Jaslabs » 10 tips for optimizing mysql queries […]
[…] Justin Silverton, en su blog When Penguins Attack nos muestra 10 puntos que podríamos seguir para optimizar el rendimiento de nuestras querys MySQL. En resumidas cuentas: […]
GNC-2007-04-10 #257…
This show is packed to with tech news and information along with a few bonus stories in the show itself. To all of the listeners out their thanks for your continued support! Sponsors: [Save 10% off on any order at……
GNC-2007-04-10 #257…
This show is packed to with tech news and information along with a few bonus stories in the show itself. To all of the listeners out their thanks for your continued support! Sponsors: [Save 10% off on any order at……
[…] 10 tips for optimizing mysql queries? - Jaslabs: High performance php (tags: howto mysql database) […]
To what extent do these generalise to other environments such as SQL Server? A list of how to optimise SQL, as opposed to sql within a specific environment, would also be interesting.
Nice post.
[…] http://www.whenpenguinsattack.com/2007/04/09/10-tips-for-optimizing-mysql-queries/ […]
The highest performance return that I’ve found recently is to turn on caching. For high read environments with repetitive queries the gains are huge.
[…] Justin Silverton @ The High Performance PHP Blog presents “10 Tips for Optimizing MySQL Queries”: 1. Use the explain command 2. Use less complex permissions 3. Specific mysql functions can be tested using the built-in “benchmark” command 4. Optimize where clauses 5. Run optimize table 6. Avoid variable-length column types when necessary 7. Use Insert delayed where you can 8. Use statement priorities 9. Use multiple-row inserts 10. Use multiple-row INSERT statements to store many rows with one SQL statement. 11. Synchronize data-types […]
[…] 10 tips to help improve the speed of your mysql queriesread more | digg story […]
[…] Read 10 Tips for Optimizing MySQL Queries (Article dated: April 9, 2007) […]
[…] 10 Tips for Optimizing mySQL Queries - i usually just ask cpradio […]
[…] » 10 tips for optimizing mysql queries? - Jaslabs: High performance php (tags: mysql optimization database sql performance tips development howto guide) […]
[…] En cualquier caso merece la pena dedicar 5 minutos a revisar algunas de las recomendaciones que encontraréis detalladas en la página del autor (en inglés): […]
[…] About a week or 2 ago, Justin Silverstone posted an article on his blog, “10 tips for optimizing mysql queries“. While not an expert list, the points hold true, however small the points may be. This was posted on digg.com and it was promoted to the front page. A few days later, someone by the name of Jesse posted an article titled, “10 Tips for Optimizing MySQL Queries (That don’t suck)“. These tips go into greater depth and are more comprehensive. However, the way he/she (Jesse is a coed name, right?) approached the problem was to correct Justin in an insulting and demeaning way. Seems very juvenile to me, but Jesse did have some very smart things to say based on what appears to be a good deal of experience. I do not want the negative part on my site, so I am cutting that part out and leaving only the smart stuff Jesse had on the subject: […]
Using stored procedures should also be on that list. Now available in MySQL.
Rofl no one uses stored procedures in mysql. It will be interesting to see how easy it will be to install these on popular web apps.
[…] 10 tips for optimizing mysql queries […]
[…] Выпуск #306 Commentful. Если вам по «долгу службы» приходится писать много комментариев в блогах или на форумах, то этот любопытный сервис наверняка придется вам по душе. Дело в том, что к сожалению не все сайты предоставляют сервис подписки на новые комментарии, а следить за дискуссией, сохраняя каждую страницу в закладках, — никаких закладок не хватит. Commentful — сервис, следящий на новыми комментариями там, где вы его попросите. Я его немного потестировал, вроде работает вполне исправно.Commentful is a service that tracks blog posts and more and notifies you whenever a new comment has been added.10 tips for optimizing mysql queries. 10 хитростей по ускорению MySQL-запросов. И еще в тему: 10 Tips for Optimizing MySQL Queries (That don’t suck).Use multiple-row INSERT statements to store many rows with one SQL statement.Interview Question and Answers. Большая подборка вопросов и задачек, которые задают при собеседовании при приеме на работу на должность программиста. Представлены вопросы по следующим языкам программирования и технологиям: ASP/ASP.NET, ADO/ADO.NET, Java, JSP, JDBC, C , C#, .NET, ORACLE, SQL, PL/SQL и т.д.Interview Question and Answers | Frequently Asked Questions (FAQs) - Whether your are fresh or experienced and you need to prepare for a job interview. Then here is the right place for you. We have selected a collections of interview questions and answers for ASP/ASP.NET, ADO/ADO.NET, Java, JSP, JDBC, C++, C#, .NET, ORACLE, SQL, PL/SQL…DHTML Arkanoid. Скучающим офисным работникам на заметку: полноценный арканоид, написанный на JavaScript и работающий практически в любом из современных браузеров.Technical Requirements / Supported Browsers HARDWARE: 800×600 screen/sound card/decent CPU (designed on a Celeron-433) SOFTWARE: Supported browser/Flash 5.0 (DHTML-driven SFX support) Desktoptwo. Операционная система, работающая прямо в вашем браузере. Пользователю бесплатно предоставляется приличный набор приложений, как то: адресная книга, RSS-агрегатор, mp3-плеер, офисные программы, доска объявлений, почтовая программа, чат и блог. Кроме того в его распоряжении окажется целый гигабайт дискового пространства, которое можно использовать по собственному усмотрению. Любопытно вот что: подобные сервисы в последнее время плодятся, как грибы после дождя. Вкупе с ростом скорости доступа в интернет — не является ли это будущим операционных систем? А что, пересядем все на тонкие клиенты, из софта на которых — только браузер, а все что надо для работы и развлечения будем получать в онлайне. Как вам такая перспектива?Desktoptwo is a free web-based desktop or webtop (some call it a WebOS, although we feel that’s a bit premature… for now) that mimics the look, feel and functionality of a local computer, all contained within one browser window and fully accessible from any Internet-connected device.Toys. Огромная подборка схем и фотографий небольших роботизированных игрушек: вертолет, рука-робот, ракета и т.д. Просто кладезь для любителей поделать что-нибудь своими руками.This can be made out of almost anything, eg a sweet wrapper; aluminium foil is good. It can be made without sellotape even but you must have a drinking straw and some means of making a hole in it. If you make them one-ended they sound like a steam train. If you have a pin, you can use it to locate the spinner over the hole instead of your finger tip. Stick it through the straw so that the spinner rests against it. Now you can make several on one straw, different types, different directions etc. Attach one to a hat with a bendy straw to blow through.Top 10 Beer Myths. Десятка самых распространенных мифов о пиве. Оказывается, пиво портится от воздуха и света, но толерантно к температуре; может быть горьким; а содержание алкоголя в пиве не зависит от его цвета.It seems like there is always that guy in the bar that has a crazy story about the beer he’s drinking. The worst part, sometimes its believable, so you tell someone, then they tell someone, and thats a beer myth. Here are ten of the more outrageous myths about beer and what you need to know to set that guy in the bar straight. […]
[…] Another 10 Tips - They just keep on trucking with those optimisation tips. […]
[…] Otros 10 consejos para optimización. […]
[…] Otros 10 consejos para optimización. […]
[…] Otros 10 consejos para optimización. […]
[…] Otros 10 consejos para optimización. […]
[…] Otros 10 consejos para optimización. […]
[…] read more | digg story […]
[…] 10 tips for optimizing MySQL queries: en inglés, recomendaciones para crear consultas. […]
[…] Another 10 Tips - They just keep on trucking with those optimisation tips. […]
[…] read more | digg story […]
Thanks, we’re doing some of these things already. Some we have not tried. We’re putting this into tektag.com/tags/mysql to share with our community.
[…] read more | digg story […]
Excellent tips, especially the “use statement priorities”. One one particularly slow site I set the inserts to low priority and selects to high and the results have been fantastic.
[…] I work with MySQL more than any other database, so naturally I want to bookmark some interesting reading. I’m currently reading 10 Tips for optimizing mysql queries and 10 Tips for Optimizing MySQL Queries (That don’t suck), both I found referenced here. Tags: MySQL […]
[…] You can optimise MySQL to save time and RAM, there’s tons of tips here for optimising your installation of mysql, and 10 more at WhenPenguinsAttack for optimising your actual queries. And don’t forget PHP, that’s one of the main things to get hammered on a site, and its also the thing which you can probably optimise till the end of time, check this out and tell me you tell me you do everything on the list 100% of the time…. […]
[…] Las consultas a MySQL […]
I thought that the LOW PRIORITY insert should not be used with MYISAM tables inside of a replication framewaork as would stop concurrent inserts.
Why then is it listed here?
What is the story with that? Are they allowed even promoted?
The MYSQL AB site says no…
Rest of your comments are very standard practices.
Maybe I am misreading something.
[…] 46. Optimizing MYSQL queries 10 Tips to optimize MYSQL queries […]
10 tips for optimizing mysql queries…
this blog post covers 10 ways to optimize your MYSQL database queries….