… temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example). One… execute EXPLAIN statement. So if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running… set (0.10 sec) Here are couple of explains if you are curios mysql> explain select * from v where i=5; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
…example. Since I now added string_crc32 to my index (now with 5 columns) my queries execute in …did it, I tried every combination testing each with EXPLAIN. Eventually you’ll find the right combination. For me… I attended a presentation by Jay Pipes on Join Fu. (http://www.jpipes.com/) You think this …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… incremented when index lookup is performed. As I explained at the start of the post that traditional index lookup (for non-index-only columns) involves, reading an index record, and… lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not going to…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… to find the correct index and here I’m going to explain three of them: A) INNODB_SYS_INDEXES Percona Server has some… the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING(TABLE_ID) WHERE t.NAME=’salaries’; +———-+———+ | INDEX_ID | NAME…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… we just need to join two previous results sets here) we can get the information about relative table index sizes and their…,”_”,t.table_name,”.key_buffer_size=”,round(least(index_length,(index_length/(select sum(index_length) from information_schema.tables where table_schema… SELECT INTO OUTFILE or pipe it to mysql directly as explained here to create key caches. Now you can use much…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…| | 2 | DERIVED | t2 | index | NULL | c1 | 5 | NULL | 2785914 | Using index | +—-+————-+————+——-+—————+——+———+——+———+————-+… count(*) from v1 join params p on p… of rows: mysql> explain select * from v2 where c1 = 10; …
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 100 Comments
…| 22771 | NULL | NULL | | BTREE | | +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ mysql> explain SELECT posts.* FROM posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…10 AND Year=2007 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime …: do not try EXPLAIN this query in InfoBright. MySQL is really stupid here, and EXPLAIN for this query…behind the curtain, they also do not have indexes like InfoBright, but results are impressive. On drawbacks -…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… be executed in various ways. For example it may use indexes or do a full table scan, or a temporary table… are the things that you can usually see by running EXPLAIN on the query. The patch will now allow you to… all the rows from a table. Full_join means any of the joins didn’t use indexes. If a temporary table is required…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… – make sure all where clauses are using indexes, try to have queries index covered and avoid joins as much as possible. ORDER BY… using Index or it can be slow on large data sets if external filesort is used so make sure to check explain… to run ORDER BY .. LIMIT can be performed using the index, for example simple JOIN is often showstopper for this.
