Percona's MySQL & InnoDB performance and scalability blog
…2, The Queries![/caption] This is part 2… The same query, run against InnoDB on… 1. row *************************** Table…column the PK, but neither of those worked. Also, trying to set innodb_ft_user_stopword_table produced the same… compare …
….t1.i rows: 26 Extra: 2 rows in set (0.00 sec) So what about Innodb ? Innodb executed the same query in 2.9 seconds… it has to read row data from OS Cache. So what if we convert i and j columns to varchar while sticking… as default ? Joining on Char columns completes in 4.5 seconds on Innodb which is about 50% slower compared to Joining on Int, for MyISAM…
…on selectivity and correlation. If where clause from first column selects 5% of the rows and applying where clause on second column…compared to single index only but it is by far better to use multi column…
…column to trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows…same time was spent on… compared … optimize | status | OK | +——–+———-+———-+——————————————————————-+ 2 rows in set (2…
… do on disk temporary tables cause compared to MEMORY tables. To have things comparable I … 5 rows in set (0.00 sec) The table contains 1.000.000 rows with mostly unique “c” column…. 0 0 5 44 96 103 2 0 94 3 0 0 3 … the same queries: mysql> set global key_buffer_size=8000000; Query OK, 0 rows affected…
…ON DUPLICATE KEY UPDATE query instances, and only one of them into table with AUTO_INCREMENT column… replication to check consistency but comparing 2 servers directly is faster … stopped both slaves at the same position again and used … check result sets. –compare-results-method rows helped to remove …
…column we see that MySQL mentions ‘const’ tables. A ‘const’ table is a table that contains 0 or 1 rows, or a table on…row. The contents of these tables are read before the query begins executing. Because of this, MySQL can compare…same…
… for each row scanned performing a lookup in the hash table by using values of the columns participating …and the OS installed is Centos 6.2 Table Structure Before moving on, let’s take a look at … compared to doing batched index lookups (BKA). However, in Test C the cost of hash join remains the same…
… the same page multiple times. Consider if you have a small buffer pool and a very large number of rows … 13384 Innodb_data_read 5G 5.4G 2.2G 5.4G 2.2G 2.2G Innodb_data_reads 329115 355323 143808 … query on MariaDB 5.5 is slow as compared to MySQL 5.6. Next interesting thing are the last two columns …
…compare query results between MyISAM FTS and InnoDB FTS over the same…column called FTS_DOC_ID which is a BIGINT UNSIGNED NOT NULL with a unique index on…dir_test_innodb | optimize | status | OK | +———————-+———-+———-+———-+ 1 row in set (4.60 …. In part 2 of this series…
Copyright © Percona LLC • Contact Us • MySQL and InnoDB are trademarks of Oracle Corp.