Thanks to all who attended my “MySQL Query Tuning” webinar on July 24. If you missed it, you can you can download the slides and also watch the recorded video. Thank you for the excellent questions after the webinar as well. Query tuning is a big topic and, due to the limited time, I had to skip some material, especially some of the monitoring. I would like, however, to answer all the questions I did not get into during the webinar session.
Q: Did you reset the query cache before doing your benchmark on your query? 0.00 seconds sounds too good
A: (This is in response to a couple of slides where the time showed as 0.00). Yes, MySQL was running with query cache disabled. The 0.00 just means that the query was executed in less than 0.004 sec. MySQL does not show the higher precision if you run the query from mysql monitor. There are a couple of ways to get the exact query times:
- MySQL 5.0 +: Use “profiling” feature: http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
- MySQL 5.1 +: Enable the slow query log with microsecond presision and log the query. To log all queries in the slow query log you can temporary set: long_query_time = 0
- MySQL 5.6: Use the new performance_schema counters
Here is the profile for an example query, the query shows 0.00 seconds:
mysql> show profile; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000064 | | checking permissions | 0.000003 | | checking permissions | 0.000006 | | Opening tables | 0.000019 | | System lock | 0.000011 | | init | 0.000031 | | optimizing | 0.000011 | | statistics | 0.000014 | | preparing | 0.000011 | | executing | 0.000002 | | Sending data | 0.002161 | | end | 0.000004 | | query end | 0.000002 | | closing tables | 0.000007 | | freeing items | 0.000012 | | logging slow query | 0.000001 | | cleaning up | 0.000002 | +----------------------+----------+
As we can see, sending data is actually 0.002 seconds.
Q: Do you ever see doing a seminar that shows how to leverage parallelization (openCL or CUDA) with databases and the performance differences?
A: MySQL does not support it right now. Usually openCL / CUDA does not help with the disk-bounded applications like databases. However, some projects in OLAP space can actually utilize openCL/CUDA, for example, Alenka, is a column store that is massively parallel. Scanning, aggregation, sorting, etc are done in a data flow manner via the CUDA processing.
Q: Is this possible to use this /covered index for order by – A.R/ with join? For example if we want to use where on table A and sort it by column from table B
A: Unfortunately, MySQL does not support that with the covered index. MySQL will only use the filter on the where condition (to limit the number of rows) + filesort. However, if we have a limit clause, MySQL may be able to use the index for order by and stop after finding N rows, matching the condition. It may not be faster thou (as I showed during the webinar) and you may have to use index hints to tell mysql to use the exact index (may not be the best approach as in some cases the use of this index may not be the best for this case). Example:
mysql> explain select * from City ct join Country cn on (ct.CountryCode = cn.Code) where Continent = 'North America' order by ct.population desc limit 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ct type: index possible_keys: NULL key: Population key_len: 4 ref: NULL rows: 10 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: cn type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: world.ct.CountryCode rows: 1 Extra: Using where
As we can see, MySQL will use index and avoid “order by”.
Q: Why are Hash Indexes not available for InnoDB engine ? Any plans to bring Hash indexes.
A: InnoDB use Hash Indexes for so called “Adaptive Hash Index” feature. InnoDB does not support hash indexes as a normal table index. We are not aware of the Oracle’s InnoDB team plans to bring this feature in.
Please note: MySQL will allow you to use “using hash” keyword when creating an index on InnoDB table. However, it will create a b-tree index instead.
Q: Will foreign key constraints slow down my queries?
A: It may slow down the queries, as InnoDB will have to
- Check the foreign key constraint table
- Place a shared lock on the row it will read:
If a
FOREIGN KEYconstraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint.InnoDBalso sets these locks in the case where the constraint fails. (http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html)
Q: How does use of index vary with the number of columns selected in a select query?
If we are talking about the covered index: if we select a column which is not a part of covered index, mysql will not be able to satisfy the query with index only (“using index” in the explain plan). It may be slower, especially if MySQL will have to select large columns and the data is not cached.
In addition, if we select a text or blob column and MySQL will need to create a temporary table, this temporary table will be created ondisk. I’ve described this scenario during the webinar.
The post Advanced MySQL Query Tuning: Webinar followup Q&A appeared first on MySQL Performance Blog.