…specific for MySQL, but note that SchemaSpy works for pretty much all the popular …Tables – Names, number of children, parents, count of columns, row counts, and comments — a great way for a high level overview of the table…review. Columns – a listing of all columns in the schema, really handy to …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…rows in `title` by a secondary index. But the result was that it created a temporary table to count…in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in…I didn’t present the details of all the tables and columns. It turned out…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
…rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL…to tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count … group by ld.label_id, cai.inventory_id All 4 tables in the query …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… MySQL replication? It’s actually much worse than Galera. As I illustrated in a blog post last year, semi-sync must serialize all… hundred rows. When a player makes an achievement, your application updates this table with a statement like this: UPDATE achievements SET count = count + 1…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode …tables with 1M rows each (about 2.1GB) for his tests, while in our original tests we used a much larger one, 16 tables…1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on …
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
…one 80×24 screen to enter in all the data! You could also… with small static rows in MySQL */ reclength=max(file->min_record_length(table_options),reclength); if (info_length+(ulong) create_fields.elements*FCOMP+288+ n_length+int_length+com_length > 65535L || int_count…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…All tests were conducted with approximately 78GiB of data (32 tables, 10M rows…substantially better, coming in at an …MySQL & Benchmark Configuration All benchmarks were conducted with the following: sysbench –num-threads=32 –test=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements for that), so it will produce all…. We can easily fill TokuDB tables with 1bln of rows on this SSD, and …test=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… provides you with all the data points you need to attack the right query in the right way. But vanilla MySQL does have… of run time, query ranked #1. The first row in the table above shows the Count of number of times this query was executed. Now let’s take a look at the values in…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
… row in set (1.17 sec) mysql> select count(val2) from fact; +————-+ | count(val2) | +————-+ | 7340032 | +————-+ 1 row in set (0.00 sec) As this is MYISAM table MySQL has cached number of rows in this table…
