The general rule of thumb for SQL queries is as follows:īe precise and generate only the results that you need. Otherwise the next Database Administrator or developer will come along and have the glorious idea of using a calculation within the query along the lines of, “oh look, my predecessor didn’t even know you can do that in SQL!” Some developer teams who have not yet had the inevitable trouble of dying databases might use in-query calculations for number differences between dates or similar data points. Just make sure you document why you’re calculating within your application rather than having a result produced in SQL right away. Write the result set somewhere else and calculate your data point outside of the query, it will put less strain on the database and therefore be overall better for your application. If you think you cannot avoid calculations within your queries: yes, you can. The things you want to avoid wherever possible and applicable are sorting and calculations within queries.

For more on the actual calculations of index vs no index, read Estimating Performance in the official MySQL documentation. If you are retrieving a huge result set from a table and querying different columns often, an index on every column does not make sense and hinders performance more than it helps. Reading the index and the table only makes sense if the table has a significant amount of rows and you need only a few data points. Beware, though, do not use too many indices as that might be counter-productive. Often, the simplest fix and quickest advice is to add an index to a specific table’s columns in question if they are used in many queries with performance issues. While it can certainly help you, it will not take away the need for structural thinking and a good overview of the data models in place. Note: If EXPLAIN does not work for you, your database user might not have the SELECT privilege for the tables or views you are using in your statement.ĮXPLAIN is a great tool to quickly remedy slow queries. Instead of the usual result output, MySQL would then show its statement execution plan by explaining which processes take place in which order when executing the statement. For a simple query, it would look like the following: EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas' In MySQL, EXPLAIN can be used in front of a query beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE. The EXPLAIN keyword is used throughout various SQL databases and provides information about how your SQL database executes a query.

Why are there 5 joins and why is there an ORDER BY used within a subquery before one of the joins even happens? Remember, you were hired for a reason - most likely, that reason also has to do with many convoluted queries that were created and edited over the last decade. You’re in your new job as Database Administrator or Data Engineer and you just got lost trying to figure out what these insane looking queries are supposed to mean and do.

Tableplus run stored function mysql