… something like this in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows… 2005!). The report explains more in detail what the “will be disallowed in a future release” means. In MySQL 5.7 the… INDEX (col2); Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2, col3); Query…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
Prior to version 5.0, MySQL could only use one index per table in a given query without any exceptions; folks that didn’t understand this limitation would often have tables with lots of single-column indexes on…=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… see with index the time is by 3.5 times slower. Good that mysql in this case choose do not use index mysql> explain select….62 sec) query with index is still 2 times slower. and this time mysql is going to use index in execution plan: mysql> explain select count… (10.51 sec) Still query without index is faster. And finally for case with 20% rows with has_someting=0 mysql> select count(name…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…queries without aggregate functions/group by and only to ones which examine one table only. What is about queries which query… a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a…. is possible. For example I might have query which uses some index scans 1000 rows and sends 10……
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…mysql> explain select count… | 81602 | Using where; Using index | …query execution: | Handler_read_next | 1305742982 | mysql…without throwing the error is not good idea in most cases, yet MySQL…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql…is disconnecting ungracefully (without closing …index or something. In this case we show the query and the information mysqlsla outputs about it (execution times, etc), the EXPLAIN…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… tables (without indexes !) which is later used further in query execution. There does not seems to be any optimizations applied to the query used… lets create a very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… using ORDER BY without and where clauses on the table. In such case you would see “Index” type in explain which correspond to… to use full index for sorting (note MySQL may not select to use index for sort if you sort full table without a limit… up queries even if data is in memory. If MySQL is only reading index and not accessing rows you will see “using index” in EXPLAIN…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…remaining results are runs without a server restart. For…query with only one join, I’ve tested performance of the query with the join in both directions. Explain for query Q1.1: mysql…indexes have been pushed out of the buffer pool. mysql>…row in set (22.54 sec) mysql> explain select straight_join sum…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… in size and having index being covering index, which means query can be executed using only data from the index without reading the row itself… as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain select…
