… this particular benchmark, because we want to see the individual affect of an optimization (where possible). Also note that we have… of how status counter values are incremented when index lookup is performed. As I explained at the start of the post that traditional index lookup (for non-index-only columns…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… index lookup would be performed on t2. Here is where Key-ordered scan comes in. With BKA turned on, the index…off ICP because I would like to see the affect of BKA on the query times. But I also…LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… preventing the insertion of data to other sessions. How to troubleshoot gap locks? Is possible to detect …id 19 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C …lot of gaps locks in your transactions affecting the concurrency and the performance you can disable them in two …
Post: How number of columns affects performance ?
September 28, 2009 By 10 Comments
… | t99v1 | index | NULL | t1 | 1 | NULL | 12000000 | Using index | +—-+————-+——-+——-+—————+——+———+——+———-+————-+ 1 row in set (0.00 sec) As you can see the index scan… rather expected as table data is stored in BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… the queries – to keep queries simple without really thinking how it affects server performance. Even worse than that – looking at the short table… artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from… table execution method. VIEWs can be used with very small performance overhead but only in case they are used with caution…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
… key order into Innodb tables for optimal performance, but effect does not end here. If indexes are built by inserts in Btree order in which entries are inserted in the index can affect performance dramatically. Table… 3600; du -sh” in the database data directory to see how much data is loaded per hour (assuming your Innodb tablespace…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… fewer tables (for the same database) you join the better performance you will get. As for any rules there are however… (A) of the index will be used which can be seriously affect performance. Of course in this example you can use index (B,A… join iteration. This does not make sense but this is how it is. I also tried to increase cardinality by having…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing… set i2=i1; Query OK, 10900996 rows affected (6 min 47.87 sec) … Index merge does helps performance but it is far from performance of combined index…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…; -> UNTIL @x > pl -> END REPEAT; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> call test_read(1000… math the row scan speed is significantly affected. I also decided to see how longer rows affect performance and created the following table: CREATE…_row_length: 129 Data_length: 685609952 Max_data_length: 948528873 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
…to see how well it performs 
One more thing to keep into account – INSERT … SELECT actually performs read …will be relaxed in future versions and how it would affect your replication. So what are safe …’re doing batch processing which is well indexed you might chop transactions and process rows …
