Archive for the ‘MySQL’ Category

MySQL Optimization – System Administration side


03 Feb

How to tweak MySQL from the System Administrators side of things. This doesn’t deal with specific queries or DBA stuff, this is more for Linux admins on how to get a lean mean MySQL machine.

  • What is MySQL Optimization?
    • These optimizations deal with changing the default variables. You would put these new and tweaked variables (aka settings)  into the /etc/my.cnf section under [mysqld] section
    • Determined by looking at MySQL logs, monitoring and using diagnostic tools
  • Why Optimize?
    • Seriously? Spend 1 or 2 days optimizing every once in a while and the results should be great; or at the very least you’ll be more confident that everything is as optimized as it can get (ever have that nagging feeling that the application just isn’t being queried properly…)
    • Optimizing improves server performance
      • Get more out of each server. Don’t just throw more servers at the problem or upgrade existing servers to mitigate the problem
    • Saves money on hardware upgrades
  • The default’s are very basic. This is because each environment and use case is unique. Think of these defaults as the MySQL “placeholder” page. You should change it to fit your needs
  • Get the current variables/settings of MySQL
    • From MySQL CLI: “show variables;”
    • From bash prompt: “mysqladmin variables” or “mysqladmin var”

Common Variables that really should be in every my.cnf file

M = megabytes ; K = kilobytes

  • Cache
    • thread_cache_size (default 0)
      • The number of threads the server should cache for re-use. It’s not even enabled by default but it should be.
      • A good starting value for a basic MySQL server is 4
    • table_open_cache (default 64)
      • This is the number of open tables for all threads
    • query_cache_limit (default 1M)
      • Limits how big of a query/result goes into the query_cache_size. So if something were to go into the query_cache, it can’t be larger than 1 MB by default. If it’s larger than 1mb it won’t be cached.
    • query_cache_size (default 0)
      • This is the amount of memory for caching queries
        • When cached it doesn’t have to keep getting pulled from the disk (speeds up results)
      • A good starting value is 8M
    • query_cache_type (default 1 = on)
      • 0 = off; 1 = on;  2 is on if needed. A specific database can call it if they want to but if they don’t call it then don’t use a query cache
      • Even though it’s on by default, the default query_cache_size is 0. So there’s no real query cache until you enable/set a size
  • Buffers
    • key_buffer_size (default 8M)
      • This is the buffer size for index blocks for MyISAM. It doesn’t include Innodb (so it won’t affect Innodb)
      • A good starting value, if you use MyISAM, is 28M (This is a very important variable to set if using MyISAM)
    • read_buffer_size (default 128k)
      • This is the thread sequential table scan buffer table size
    • join_buffer_size (default 128k)
      • The minimum size of the buffer that is used for plain and range index scans and for joins without index scans, and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. If you can’t use Index’s for some reason, then increase the value of to get a faster full join. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary
  • Others
    • connect_timeout (MySQL default is 10)
      • Number of seconds MySQL will wait for a packet before it sends a bad handshake.
      • The reason you may want to reduce it is because you have a very active server and you have very solid connectivity between the application and the server and you just don’t want a lot of hosts connecting to you that are taking too long. So in order to keep a lot of those connections going, you reduce.
      • The reason you may want to increase it is maybe you have a slow server, or the network connectivity isn’t that great or prone to issues (like having your traffic deprioritized) and because its latent or the connectivity is slow you don’t want a lot of people getting bad pages on your machine because your connect_timeout is set too low.
    • max_connect_errors (default 10)
      • This is the maximum number of interrupted connections from a host. The default being 10, in order for a host to reach that they’d have to send a packet and then interrupt it before its completed. They’d have to do it 10 times in a row before they are permanently blocked.
        • Once they are permanently blocked they won’t be able to connect to the server again (until you run flush hosts; from the command line)
        • If you have a bad network connection or periodically bad and people continually have problems connecting, you may want to increase this or they’ll become permanently banned.
        • If you were getting a MySQL based attack, and they’re purposely interrupting the MySQL connections in order to try and take the server down, you may want to lower this number (since they’ll be using a lot of various IP’s, connecting frequently).
    • max_allowed_packet (default 1M)
      • This is the maximum size of the packet that can connect.  The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.
      • You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.
      • Developers will often ask you to increase this value, especially if they’re storing a lot of data in the database
      • A good starting value may be 10M
    • tmp_table_size (system-specific default)
      • The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.
      • The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-diskMyISAM table.
      • Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

 

Unneeded Variables

These are unneeded because they’re already the default, or they don’t exist on your system.

  • thread_concurrency
    • It doesn’t exist on Linux (or FreeBSD). Only on Solaris.
    • A lot of guides mention this variable. If you see it, remove it. MySQL 5.5 and 5.6 won’t even start up if you have this variable defined in your config.
  • skip-locking
    • You can have it in your file if you want but there’s no reason to. Default skipped since MySQL 4.0
    • It’s actually called skip-external-locking
    • You’re not hurting anything by having it, but the goal of optimizing is only to have the variables/changes you need. If you don’t need it, it shouldn’t be in there.

 

Logging

  • Why log?
    • Only way to track down intensive databases or database users. It’s the only way you’re going to know historically over the last day/week/month which databases have been running processes and are showing up in the log.
    • Without it, have to rely on monitoring
  • Types of logging
    • Error
      • It logs MySQL errors
      • Default is enabled
    • General
      • All databases process activity
      • Default is disabled
      • This is very DiskIO intensive and very tedious to parse through. I wouldn’t do this unless you’re trying to track down a problem or it’s dev/qa
    • Slow Query
      • Slow MySQL queries (10+ seconds to execute by default, this can be changed)
      • Default is disabled
      • If you’re doing query optimization, this is very important to enable (mainly in Dev/QA environments; I don’t recommend doing this in production as it can slow things down, but if you must, do it for a brief period of time and then turn it back off)
      • Databases the frequently perform slow queries are going to cause terrible latency, terrible slowdown for MySQL on the server and increase load
    • In MySQL 5.5 and 5.6 to enable General or Slow Query logging
      • In /etc/my.cnf add “general_log” to the [mysqld] section
        • Remember, this is Disk IO intensive and these logs take a lot of space, so be careful when using this.
      • In /etc/my.cnf add “error_log” to the [mysqld] section
        • This isn’t too Disk IO intensive so it’s not as bad as general logs. If you feel it’s still generating too much activity, you could change the number of seconds a query takes (10 by default) before it gets added to the slow_query log. Then as you fix those queries and the log starts to slow down, you can decrease the number a little more and then address those slow queries, etc. Essentially start at the top, fix it and then scale down; rinse and repeat.

MONITORING

  • top – Almost every linux server has this
    • this is a basic overall real-time system monitor, not MySQL specific but is present on any decent Linux server.
  • atop – Most linux distro’s have this in their package/repository list for easy installation
    • I prefer this over top because it shows you a lot more details about the system and it can even be run as a background daemon and it’ll grab snapshots of your system ever few minutes so you can monitor processes for Disk/Memory/etc. over time. atop rocks.
  • mtop – http://mtop.sourceforge.net/  (may have to install from source)
    • mtop (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. Features include ‘zooming’ in on a process to show the complete query, ‘explaining’ the query optimizer information for a query and ‘killing’ queries. In addition, server performance statistics, configuration information, and tuning tips are provided.
    • Kind of an oldie but goodie. I’ve only used it on MySQL 5.1 and 5.5, not sure about 5.6
  • innotop – https://github.com/innotop/innotop  (may have to install from source)
    • innotop is a ‘top’ clone for MySQL with many features and flexibility. It’s completely customizable; it even has a plugin interface.
    • innotop monitors MySQL servers. Each of its modes shows you a different aspect of what’s happening in the server. For example, there’s a mode for monitoring replication, one for queries, and one for transactions. innotop refreshes its data periodically, so you see an updating view.
    • monitors many servers at once and can aggregate across them
  • MySQL Monitoring Tool
    • mysqladmin processlist / mysqladmin proc  (or most likely ‘mysqladmin -u root -p processlist’)
    • This is MySQL’s own processing tool. It shows you details about what’s going on within the mysqld process. It shows you Id/User/Host/db/Command/Time/State/Info
  • check_mysql_health – Nagios plugin for monitoring various MySQL health aspects
    • MySQL_connection-time – ./check_mysql_health –socket /var/run/mysqld/mysqld.sock –user root –password rootpassword –database somedatabase –mode connection-time
    • MySQL_Uptime – ./check_mysql_health –socket /var/run/mysqld/mysqld.sock –user root –password rootpassword –database somedatabase –mode uptime
    • MySQL_threads-connected – ./check_mysql_health –socket /var/run/mysqld/mysqld.sock –user root –password rootpassword –database somedatabase –warning 30 –critical 40 –mode threads-connected
    • MySQL_threadcache-hitrate – ./check_mysql_health –socket /var/run/mysqld/mysqld.sock –user root –password rootpassword –database somedatabase –warning 100 –critical 110 –mode threadcache-hitrate
    • You can do the same as above for entries like: MySQL_table-lock-contentionMySQL_index-usageMySQL_tmp-disk-tablesMySQL_slow-queriesMySQL_long-running-procsMySQL_sqlMySQL_open-filesMySQL_encode and MySQL_querycache-hitrate

TEMPLATES

  • You’d fine them in /usr/share/doc/mysql-server-5.5/examples/ as names like “my-huge.cnf.gz” “my-large.cnf.gz” “my-small.cnf” “my-innodb-heavy-4G.cnf.gz” and “my-medium.cnf.gz”  ;  the ones with the gz extension would need to be decompressed first using gunzip
  • Don’t use them. They’re not very good templates. They were created a long time ago and have not been updated recently.
    • They even include the thread_concurrency unneeded variable I mentioned above, the one that will stop MySQL 5.5 and 5.6 from starting up
    • They change a lot of variables without you really understanding what each does
    • Performance improvements the template provides over the defaults is very questionable
  • Just change one or 2 variables at a time, knowing what each does and seeing exactly how it affects the database performance. It’s the best way to know your database/server inside and out and know what each and every setting does and how much it helped.

 

DIAGNOSTIC TOOLS

  • General Optimization
    • mysqltuner.pl
      • Checks MySQL activity since the last restart of MySQL (so it won’t help if you’ve recently restarted mysql; wait at least 24 hours)
      • Provides recommendations by comparing the activity with the current /etc/my.cnf settings
      • The output provides exact values for the recommended settings to add to /etc/my.cnf
        • You can generally do what it recommends, but you should lookup each variable in the MySQL documentation and get a general idea of what that variable does and what its normal default is and what your’e changing it to. Just don’t blindly trust the script unless you’re in a big hurry and you really don’t care too much about the server or the data.
    • mysqlidxchk
      • mysqlidxchk (MySQL Index Checker) checks MySQL databases/tables for unused indexes. It reads in the slow or general log files
      • mysqlidxchk reports which indexes in the database schema are not used by the queries in the log files
      • If feasible, you should then go in and unindex those databases. Removing index’s that aren’t needed speeds up databases.
      • There are some thing that need to be indexed (that haven’t been; that you should index ) but if it’s not needed, don’t index it.
  • Log parsing
    • mysqlsla
      • Uses the general or slow query logs to track the database users percentage-wise
      • Let’s say you enabled slow or general logs 2 days ago, if you go and run this against the logs it will provide the top 10 users (and show you each users percentage usage) and tell you what percentage that database user is of the queries in the log. Lets say you have the top most user, and they’re 30% of the  slow query activity; there’s something seriously wrong with that database. You could move that user or database to another MySQL server and speed up every other database/user using that server.
  • Status reporting
    • mysqlreport
      • It interprets MySQL’s ‘SHOW STATUS’ values into a detailed report on how MySQL is running
      • It’s a really complicated tool, read over their guide in detail

 

STRESS TESTING/BENCHMARKING

  • mysqlslap – quick/common tool for generating quick load
    • mysqlslap –user=root –auto-generate-sql –concurrency=100 –number-of-queries=1000 –number-char-cols=10 –number-int-cols=10 –iterations=100 >> /tmp/mysqlslap.log
    • You can put the above in a cronjob or better yet, a script that runs it in a loop. You can keep increasing some of those numbers in the command until you get a nice consistent high load on the dev/test server. You could let it run for ~24 hours if you were going to use a script like mysqltuner.pl to get some good better-overall default settings for your my.cnf file for that particular server.
  • sysbench – Excellent utility for running an actual benchmark and providing you with the number of transactions per second value
    • To measure MySQL performance, we first create a test table in the database test with 1,000,000 rows of data
      • sysbench –test=oltp –oltp-table-size=1000000 –mysql-db=sysbenchtest –mysql-user=root –mysql-password=yourrootsqlpassword prepare
    • Then we run the actual benchmark
      • Read-Write – 16 threads
        • LOCAL: sysbench –db-driver=mysql –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=1000000 –mysql-db=sysbenchtest –mysql-user=root –mysql-password=yourrootsqlpassword run
        • REMOTE: sysbench –db-driver=mysql –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=1000000 –mysql-db=sysbenchtest –mysql-host=<mysql server hostname> –mysql-port=<mysqlport> –mysql-user=<mysqluserid for database> –mysql-password=<mysqluserid password> run
      • Read-Only – 8 threads (good for testing read replicas/slaves)
        • sysbench –test=oltp –oltp-table-size=1000000 –mysql-db=sysbenchtest –mysql-user=root –mysql-password=yourrootsqlpassword –max-time=60 –oltp-read-only=on –max-requests=0 –num-threads=8 run
    • The important number is the transactions per second value:  ie “transactions:  100001 (269.36 per sec.)”
    • Latency makes a very big impact on transactions per second. The difference between a separate but local database server and one that was 20ms or 60ms away was fairly substantial, even if the remote server was beefier. I don’t recommend being more than 20-25ms away from your database server, if you have to be, you need to have some kind of local read-replica/slave copy for reads or you need to have your application do some heavy caching and minimize the usage of the mysql server. You could also use connection pools to help, have the application establish and keep-alive/maintain x number of active connections to the mysql server and then just push all of its queries across that pool. This will help with TCP tear-up/tear-down’s (SYN, SYN-ACK, ACK-ACK, PUSH, FIN, FIN ACK, etc. only to come around a split second later and do it all over again)

MySQL query optimization – DBA side


03 Feb

Note to self: Brain Dump. Needs to be organized better.

This relates to how the application does query’s to the MySQL server. This is more DBA/Application side. But here are some good general rules to follow. FYI I took these from my own personal emails and notes that I’ve taken over time. Some of it is from an old abandoned Wiki I used to run. I’m sure my notes/emails/wiki/etc. were generated from some content on the net but I can’t always remember where. I apologize if I’ve offended anyone by not citing source, but if you know the source of any of these various sections below feel free to comment it and I’ll be happy to edit this post and cite the source.

  • Use EXPLAIN keyword to see the execution plan for the query. 2 key elements to watch for in the execution plan are
    • Check the index usage (key column in the EXPLAIN output)
    • Check rows scanned for query
    • See the EXPLAIN section below this section, because EXPLAIN is a powerful and complex utility for analyzing a query.
  • If you’re retrieving a unique/single row add “LIMIT 1” to the end anyways
    • You can also use LIMIT to improve aggregation functions like MIN or MAX
  • If your query uses ‘not equal to’ operator try to convert that to ‘equal to’ operator (Try to convert <> operator to = operator)
    • ‘equal to’ operator (=)  increases the chances of your index to be used for your query
  • Avoid using “SELECT *” unless you are retrieving all the columns of your table
    • “SELECT *” forces a full table scan by ignoring index’s to execute the query
    • Additionally, columns which your application doesn’t need wastes your network bandwidth (it may seem like a little bit but multiply this by thousands of queries per second and it adds up)
  • Split big DELETE, UPDATE or INSERT queries into multiple smaller queries. It improves the performance as well as give you better control on data
  • Use appropriate data types for columns. If you are going to only store integer’s don’t create your column with data type varchar
    • Smaller columns are faster for performance so don’t use overly large columns
  • MySQL query cache is case and space sensitive! That means if you are going to get the same result set multiple times, make sure to use the same case and spacing between words. This dramatically improves your query performance because MySQL engine will be able to return your result set with regard to index.
  • If you create index on the column in which you use the WHERE clause it’ll improve performance.
    • However this is just general/generic advice. Don’t go nuts creating too many index’s, that’s also dangerous. Create Index’s on columns you’ll be doing WHERE clauses on the most, not every single time. Use common sense.
  • Create Index columns on the columns in which you have used in JOIN
    • This is a general guideline. Again, don’t create an index on every column you used in your join. Use your best judgement, and test your queries before you create too many index’s.
  • Use ‘UNION ALL’ instead of ‘UNION’ if duplicate data is permissble in your result set. ‘UNION ALL’ gives better performance than ‘UNION’ because it does not have to do a distinct operation on your query
  • If you are going to use just INNER JOIN the table order used in your JOIN clause does not matter
  • If column used in ‘ORDER BY’ clause are indexed it helps with performance of the query (when you are doing sharding, one of the most expensive operations of MySQL)
  • If your application implements pagination, it is a good idea that you use the LIMIT clause to implement paging in your database itself. Not only does this improve performance but it also reduces database traffic between your database and client application (again, this adds up over thousands of queries).

There can multiple ways to write a query to produce the same result set. It is possible one method works for one query while another method works for another query.

Always test your query with near real data and schema on your development server before deploying to your production server. This is very important because what speeds up query’s varies largely on the type/size/etc. of data being queried, so you could find one of the above helps tremendously on your test data and then roll it into production and find it doesn’t help very much or at all (I doubt it’d make it worst; but maybe…).  It’ll look bad if you say “We did this optimization in our test lab and it improved things by 25%” and you go and roll it out in production and you only get a 2% increase. You could have used that deployment roll out time to do something else that would result in better performance.

If you production database is always changing / always updated, you could try creating a snapshot of the database and restoring the snapshot into a QA/Test environment or you could try restoring the latest backup of the production database (you DO have periodic backups don’t you…)

 

EXPLAIN

The number one utility for analyzing a query. EXPLAIN shows how MySQL thinks, how it anticipates it’s going to execute a query. This is not the true execution plan MySQL might choose, in many cases it is though. There are cases where MySQL thinks it is going to execute a certain way but then when it gets started with a query it finds out the values it’s examining is different than it thought, so it may actually re-evaluate and re-plan as it goes. Sometimes you can see in advance that MySQL knows it’s going to have to re-plan the query for every value it retrieves from the table. So take the results/information EXPLAIN presents to you with a grain of salt, it’s ‘generally accurate’ but not always.

If the MySQL query is doing a JOIN from one table into another and MySQL doesn’t really know which index is going to be best, if it thinks that different index’s might be best depending on the values it retrieves from the first table it might actually switch around and re-evaluate (the index to use) every time it looks for rows in the 2nd table. EXPLAIN may or may not be able to accurately display this (because it hasn’t done the actual query so it wont’ know the actual values)

EXPLAIN only works for SELECT in MySQL 5.5 and previous
EXPLAIN will additionally work with UPDATE, DELETE, INSERT and REPLACE  in MySQL 5.6

  • Generating EXPLAIN
    • MySQL actually executes the query (with a special bit that tells it not to execute, explain only)
      • But at each JOIN, instead of executing, it fills the EXPLAIN result set
    • What is a JOIN?
      • Everything is a JOIN, because MySQL always uses nested-loops
      • Even a single-table SELECT or a UNION or a subquery is a JOIN
    • Because it actually executes the query, but because of that special bit it doesn’t actually do anything. However this is not necessarily true for subquery’s.
      • subquery’s that require a materialized temporary table as a step in the input to the execution plan, will actually cause MySQL to execute the subquery and create the temporary table and fill it with the results and then re-execute the outer query against the subquery.
      • If you EXPLAIN a query that has a subquery and you notice EXPLAIN is taking a while to finish, that’s because it’s executing that inner table and filling that temporary table.
    • EXPLAIN output/columns
      • id: which SELECT the row belongs to
        • If only SELECT with no subquery or UNION, then 1
        • Otherwise, generally numbered sequentially
        • Simple/complex types
          • Simple: there is only one SELECT in the whole query
          • 3 subtypes of complex:
            • subquery: numbered according to position in the SQL text
            • derived: executed as a temp table
            • union: fill a temp table, then read out with a NULL id in a row that says UNION result
      • select_type
        • Simple vs complex; which type of complex
        • Special UNION rule
          • First contained SELECT matches outer context
        • Dependencies, uncacheability
          • Refers to the item_cache, not query cache
      • type
        • How MySQL will access rows (how it will find rows in the table)
        • Worse to better:
          • ALL – Table scan where it scans through the entire table looking for rows
          • index – Scan an entire index, kind of like a full table scan except it’s only a full index scan
          • range – Scan a range of an index
          • ref – value as a reference to look into and find rows in the index that match that reference value
          • eq_ref – Variation of reference, there’s going to be at most 1, so that’s a unique index / primary key
          • const – Constant
          • system – MySQL has completely, during the optimization phase, determined that it can replace this access to this table with a constant and include that constant in the rest of the query as though it wasn’t a table at all (example: selecting the maximum value from an indexed column; MySQL can seek to the end of the index during the execution planning phase and find out what the maximum value in that column is and then instead of referring to the query against the index later in the query it can actually refer to that value)
          • NULL – There’s no table at all involved. (ie “select 1”)
      • ref
        • When something is looked up in an index, when MySQL is seeking for rows that match a value, where does it get that value from.
      • rows
        • Approximately how many rows MySQL thinks it’s going to examine. This is based on the storage engine statistics. There are 2 function calls MySQL can make during query planning to the storage engine to ask it ‘what is the distribution of rows in your index tables, and how many tables do you estimate are going to match this value’
        • That value gets used in query execution and optimization and evaluating different orders that might be used (like for a JOIN)
      • filtered (only shows in MySQL 5.1)
        • The percentage of rows that satisfy WHERE
        • Usually 0 or 100; complex behavior
        • Kind of useless, doesn’t show you all that much, that’s why it was removed in MySQl 5.5 and newer
      • extra
        • Using index
        • Using where
        • Using temporary
        • Using filesort
        • Sort-merge types
        • Lots more values (check the documentation)
    • EXPLAIN Index-Related columns
      • possible_keys
        • Which indexes were considered? Which were candidates.
          • Sometimes MySQL will actually use an index that wasn’t listed as a candidate because the decision of which things are candidates is decided in one point of the source code and somewhere else there is an optimization that could choose a different index.
            • So you may sometimes see an index listed in key (which one was used) but not listed in the possible_keys (which were considered)
      • key
        • Which indexes did the optimizer choose?
      • key_len
        • How many bytes of the index will be used?

 

  • How does MySQL execute queries?  In a very broad sense…
    • Parses SQL => (data structure) Parse Tree => (data structure) Execution Plan
    • Executor (aka executioner) works from the data structure, the execution plan
      • It goes through the execution plan structure, navigating from part to part in the execution plan data structure and executing the storage engine API calls on each node in that data structure
      • Optimizations become changes to the tree structure of the execution plan, constant folding, etc. (looks like what a compiler does)
      • The execution plan is just another type of data structure, not byte-code (which could then be handled higher up by the CPU)
      • The executor makes storage engine calls

 


What is Explain? It shows the estimated query execution plan. Only for SELECT in MySQL 5.5 and previous. It might not be the true execution plan, but it could be; as it gets started out it may learn values that it's examining is different than it thought and it may reevaluate / re-plan the query.  Sometimes you can even see in advance that MySQL knows it'll have to re-plan the query for every value it gets.

If it’s doing a join from one table to another and it doesn’t really know which index is going to be best, it thinks that different index might be best depending on the values it gets from the first table it may switch around and reevaluate the index to use every time it looks for rows in the 2nd table. It’s only an estimate and it’s only for select.

In MySQL you can’t really see how the query executed after the fact.

EXPLAIN is an estimate of how MySQL thinks it is going to optimize and execute the query.

In MySQL 5.6 you can actually be able to say ‘explain update’ ‘explain delete’ ‘explain insert’ ‘explain replace’

Execution plans and how we end up generating the EXPLAIN output. MySQL’s execution process, broadly speaking, is that it parses SQL, converts that into a data-structure called the “Parse Tree” and then transforms the parse tree into the Execution Plan. The executor (called the executioner) works from that data structure (the execution plan) it iterates through the structure navigating from part to part in the data structure and executing the specified storage engine API calls on each node in that data structure.

MySQL starts with a data structure that come sout of the parse process and it transforms it into another data structure. Optimizations become changes to the tree structure, changes to the execution plan, etc.

MySQL JOIN only combienes 2 pieces of information from tables at the same time. So if you’re joining 3 tables, it joins the first 2 tables first, then adds in the 3rd table. If you joined 4 tables, it’d join table 1 and 2, then 12 + 3 then 123 + 4.


During an EXPLAIN MySQL actually executes the query. But at each JOIN, instead of executing, it fills the EXPLAIN result set. Most of the time it’s a NOOP (bit is set that says don’t do anything) but this is not necessarily true for subqueries (subqueries that require a materalized temporary table as a step in the input to the execution plan will actually cause mysql to execute that subquery and fill a temporary table with the results and re-execute the outter query with the subquery). So if you EXPLAIN a query that has a subquery, sometimes you’ll see EXPLAIN takes a long time to finish. This is because it’s executing the inner query to fill the tbale before it explains the outter query.

2 classes of query’s, SIMPLE and COMPLEX. Simple is what you used to be able to do in MySQL v3 (before version 4 introduced subquery’s and unions). Subquery’s and union’s are considered COMPLEX.

For the “id” column. Primary queries|top level queries|outter queries have an ID of 1 (in the output of EXPLAIN). ID = Which select the row belongs to. If only SELECT with no subquery or UNION, then 1. Otherwise, generalyl numbered sequentially. 3 tubetypes of complex:
– subquery: numbered according to position in the SQL text
– derived: executed as a temp table
– union: fill a temp table, then read out with a NULL id in a row that says UNION RESULT


NUMERIC TYPES
TINYINT: 127 to -128
SMALLINT: 32,768 to -32,767
MEDIUM INT: 8,388,608 to -8,388,608
INT: 2^31 to -2^31 -1
BIGINT: 2^63 to -2^63 - 1
FLOAT: Decimal spaces, 1.1E38 to -1.1E38
DOUBLE: Decimal spaces, 1.7E308 to -1.7E308

STRING TYPES
CHAR: a character string with a fixed length
VARCHAR: A character string with a length that’s variable
BLOB: Can contain 2^16 bytes of data
ENUM: A character string that has a limited number of total values, which you must define.
SET: A list of legal possible character strings. Unlike ENUM, a SET can contain multiple values in comparison to the one legal value with ENUM

DATE & TIME TYPES
DATE: YYYY-MM-DD
TIME: HH:MM:SS
DATETIME: YYYY-MM-DD HH:MM:SS
TIMESTAMP: YYYYMMDDHHMMSS
YEAR: YYYY

Deon's Playground

Placing whatever interests me and more