…5.6.10 sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0.1:5610…between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!),…all columns in the schema, really handy to sort by name to see if you have …
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… Benchmark. In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using… at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used… with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… a good tradeoff. Q: Is the tuple comparison independent of sorting order? Wouldn’t it be a problem if the keywords… in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the…
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….organized in an unidirectional list. The list is sorted by primary key and starts with the …page: ./constraints_parser -5f pages-actor/FIL_PAGE_INDEX/0-1599/00000000-00000003.page -V … Checking a…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… PRIMARY KEY index entry for that row). This means typically on a single Innodb node we can have all sorts modifications floating… no effect on standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication? It’s actually… to the tradeoffs we take when deploying conventional stand-alone MySQL Innodb with asynchronous slaves. We may not think about the…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_r10…=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on…_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… prepdefined A: MySQL Server will not automatically define any indexes for you. Hopefully your CMS already comes with reasonable set of indexes, if… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or… column. This works because by scanning data in index order MySQL gets data in already sorted order and looks at “one group at…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… further index parts. If you have A BETWEEN 5 AND 10 AND B=5 for the same index MySQL will use the index… but… how MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index… be able to use full index for sorting (note MySQL may not select to use index for sort if you sort full table without a limit…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql… VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… to other products besides MySQL. The logical principles still hold true: using an index really means leveraging a pre-sorted data structure to… that MySQL can use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index condition…
