…/share/java/mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent… additional tabs: Tables – Names, number of children, parents, count of columns, row counts, and comments — a great way for a high level…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the IDs for… to count the movies per production year for each kind_id. It was more efficient in this case to force MySQL to… table, and making lookups to the `kind_types` table by primary key, the result was a query that took half the time…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement transactions (TPS… use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… is a single row (well, the PRIMARY KEY index entry for that row). This … MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL …? if( $last_count % 100 == 0 ) { $db->do( “UPDATE achievements SET count = $last_count where achievement = ‘killed_troll…
Post: Finally. How to verify if all MySQL records were recovered
April 25, 2013 By Leave a Comment
…recovery case the same question arises: How many MySQL records were recovered and how many were lost….records the page stores. Obviously it doesn’t count any deleted records. The second method to …an unidirectional list. The list is sorted by primary key and starts with the internal record infinum and…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite…, $ID/1000, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is…=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…PRIMARY KEY (`id`)) —————————————————————————————————— $mysql=mysql_connect(’127.0.0.1′,’root’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $level) { $result = mysql…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… make primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key appended…, nothing more. Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | 1 | SIMPLE | a | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | b | const | PRIMARY | PRIMARY… is “const” MySQL does not count it as access…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count(*) pages…), key(P_Name), key(P_MFGR), key(P_Category), key(P_Brand) ); DROP TABLE IF EXISTS supplier; CREATE TABLE supplier ( S_SuppKey int primary key, S…
