Jaslabs: High performance Software

High Performance Software

10 tips for optimizing mysql queries

April 09th, 2007 | Category: mysql

By Justin Silverton

  1. 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).
  2. use less complex permissions

    3. 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.

  3. 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

  4. 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
  5. Run optimize table

    6. This command defragments a table after you have deleted a lot of rows from it.

  6. avoid variable-length column types when necessary

    7. 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.

  7. insert delayed

    8. 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.

  8. 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.
  9. use multiple-row inserts

    10. Use multiple-row INSERT statements to store many rows with one SQL statement.

  10. synchronize data-types

    11. 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.

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • del.icio.us
  • DZone
  • Slashdot
  • StumbleUpon
  • Technorati

47 Comments so far

  1. I’m Mike » Blog Archive » How not to optimize a MySQL query April 9th, 2007 8:21 pm

    […] 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 […]

  2. smalls blogger » Blog Archive » 10 Tips for Optimizng MySQL queries April 9th, 2007 10:27 pm

    […] 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 […]

  3. Sherif Mansour April 9th, 2007 10:28 pm

    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

  4. WTL April 9th, 2007 10:32 pm

    Thank you - I’ll give a few of those a try and see how much the improve performance.

  5. all these things that i’ve done » 10 tips for optimizing mysql queries April 9th, 2007 11:25 pm

    […] read more | digg story […]

  6. Karl Katzke » 10 MySQL Performance Tips April 10th, 2007 1:16 am

    […] 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 […]

  7. Matt Montgomery April 10th, 2007 1:57 am

    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

  8. Sean April 10th, 2007 2:07 am

    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.

  9. Simone D. April 10th, 2007 3:34 am

    A very interesting article, often SQL optimization is not a priority … but it would be :)

  10. Jaslabs » 10 tips for optimizing mysql queries « Gatika(blog.com) April 10th, 2007 3:59 am

    […] Link Original: Jaslabs » 10 tips for optimizing mysql queries […]

  11. DiarioLinux » Blog Archive » 10 consejos para optimizar las consultas en MySQL April 10th, 2007 4:04 am

    […] 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: […]

  12. Geek News Central Podcast April 10th, 2007 5:09 am

    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……

  13. Geek News Central April 10th, 2007 5:13 am

    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……

  14. oz4.org - links for 2007-04-10 April 10th, 2007 6:26 am

    […] 10 tips for optimizing mysql queries? - Jaslabs: High performance php (tags: howto mysql database) […]

  15. Web Design Glasgow April 10th, 2007 6:27 am

    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.

  16. 13.vc » Blog Archive » 10 tips for optimizing mysql queries April 10th, 2007 6:36 am

    […] http://www.whenpenguinsattack.com/2007/04/09/10-tips-for-optimizing-mysql-queries/  […]

  17. Michael Glenn April 10th, 2007 8:18 am

    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.

  18. WebDevTools » Blog Archive » 10 Tips for Optimizng MySQL queries April 10th, 2007 8:34 am

    […] 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 […]

  19. Domester.NET Blog » 10 tips for optimizing mysql queries April 10th, 2007 11:09 am

    […] 10 tips to help improve the speed of your mysql queriesread more | digg story […]

  20. 10 Tips for Optimizing MySQL Queries / Web Words / WizarDev April 12th, 2007 8:45 am

    […] Read 10 Tips for Optimizing MySQL Queries (Article dated: April 9, 2007) […]

  21. Links for the Weekend, 4-14-2007 April 14th, 2007 12:50 pm

    […] 10 Tips for Optimizing mySQL Queries - i usually just ask cpradio […]

  22. mcdave.net » links for 2007-04-15 April 15th, 2007 2:23 am

    […] » 10 tips for optimizing mysql queries? - Jaslabs: High performance php (tags: mysql optimization database sql performance tips development howto guide) […]

  23. 10 consejos para optimizar las consultas en MySQL « XperimentoS April 15th, 2007 3:16 pm

    […] 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): […]

  24. Bill’s Dev Blog » Blog Archive » 10 Tips for Optimizing MySQL Queries April 17th, 2007 10:19 am

    […] 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: […]

  25. Yuzle April 22nd, 2007 6:40 pm

    Using stored procedures should also be on that list. Now available in MySQL.

  26. Motorcycle Guy April 26th, 2007 1:17 pm

    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.

  27. 10 consejos para mejorar tus queries MySQL « Disenia May 7th, 2007 12:33 pm

    […] 10 tips for optimizing mysql queries […]

  28. Тут Хумора.NET » Выпуск #306 May 28th, 2007 9:05 am

    […] Выпуск #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. […]

  29. Straw Dogs Code Blog » 10 Indispensible MySQL Resources July 28th, 2007 8:37 am

    […] Another 10 Tips - They just keep on trucking with those optimisation tips. […]

  30. 20 recursos para mySQL August 1st, 2007 6:40 am

    […] Otros 10 consejos para optimización. […]

  31. 20 Recursos Para Trabajar Con MySQL August 2nd, 2007 2:54 am

    […] Otros 10 consejos para optimización. […]

  32. Actualidad, Entretenimiento y Humor » 20 Recursos Para Trabajar Con MySQL August 2nd, 2007 3:55 am

    […] Otros 10 consejos para optimización. […]

  33. 20 Recursos MySQL « Neozeratul en la Red August 9th, 2007 12:19 pm

    […] Otros 10 consejos para optimización. […]

  34. ITVO Alternativo :: 20 Recursos para gestionar Bases de Datos en MySQL :: August :: 2007 August 15th, 2007 8:47 am

    […] Otros 10 consejos para optimización. […]

  35. Orkut » Blog Archive » 10 tips for optimizing mysql queries September 1st, 2007 9:15 am

    […] read more | digg story […]

  36. Consejos y manuales sobre MySQL « Sistemas Operativos September 30th, 2007 6:25 am

    […]  10 tips for optimizing MySQL queries: en inglés, recomendaciones para crear consultas. […]

  37. Straw Dogs » 10 Indispensible MySQL Resources September 30th, 2007 10:40 am

    […] Another 10 Tips - They just keep on trucking with those optimisation tips. […]

  38. 10 tips for optimizing mysql queries « Programming News October 11th, 2007 9:00 am

    […] read more | digg story […]

  39. TJ October 17th, 2007 6:22 pm

    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.

  40. I am just a programmer » 10 tips for optimizing mysql queries November 20th, 2007 11:42 am

    […] read more | digg story […]

  41. Code4Gold December 10th, 2007 6:23 pm

    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.

  42. MySQL Optimizing Tips, Best Practices | David Bisset: Web Designer, Coder, Wordpress Guru December 20th, 2007 2:51 am

    […] 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 […]

  43. nuefoo / How to help stop Digg from murdering your precious server. [Web Design • CSS • Web 2.0 • PHP] January 13th, 2008 7:47 pm

    […] 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…. […]

  44. Optimizando… » ingeniuz :: desarrollo web útil January 14th, 2008 12:57 pm

    […] Las consultas a MySQL […]

  45. njelly January 16th, 2008 5:23 pm

    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. 100+ Resources for Web Developers | BlogWell March 4th, 2008 10:04 am

    […] 46. Optimizing MYSQL queries 10 Tips to optimize MYSQL queries […]

  47. socialcmsbuzz.com March 7th, 2008 8:09 pm

    10 tips for optimizing mysql queries…

    this blog post covers 10 ways to optimize your MYSQL database queries….

Leave a reply