… database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. It’s also designed to… Percona Server 5.6.10 sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0.1:5610/schemaspy driverPath…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that sometimes… queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be improved. But there’s a finite amount…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… – MySQL parameter testing The first test was designed to look solely at vCache performance under some different sets of MySQL …–percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=innodb \ –oltp-read-only=off run The base MySQL configuration (…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication…other nodes, a GTID will also (by design) be established. We’ll never end … maintained in separate tables and there also exists a users_groups table to define the relationship …
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… Percona XtraDB Cluster (PXC) for MySQL. I actually had more material than I covered (by design), but one thing I regret we… replication feedback mechanism, unlike anything you find in standard async MySQL replication. It is my belief that the lack of understanding… key lookups into the certification index, which is a hash table. A small fc_limit does however keep the certification index…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
… memcached is that it is not persistent. While a common design consideration when using a cache layer is that “data in… nutshell, here is how one would start the memcached plugin: mysql> install plugin daemon_memcached soname ‘libmemcached.so’; In an effort… per/cmd 0.0951 ms range per/cmd InnoDB MySQL Select (same table): Fetching [1,000,000] items: 441573 ms total 0…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… querying trees in an SQL database, including the design I call Closure Table. In Closure Table, we store every path in a tree…; Because there isn’t much written about using the Closure Table design, I periodically get questions about how to solve certain problems… and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find…
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
…’t something you should expect to deliver huge wins. If MySQL is actually badly configured, you can hurt its performance significantly… cases, so keep in mind that the vast majority of MySQL installations don’t suffer from the variety and severity of… hardware The server configuration The workload The queries The schema design (table design, data types, indexing, etc) The data itself All of these…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… versions of MySQL. Q: How do the upcoming changes in MySQL 5.6 change the best practices for index design? Fundamentally, index design best practices will not change. MySQL 5.6… sense for your table. Logically, your index design will be the same with a partitioned table as with a non-partitioned table. But since…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… ? The reason is normally table design and understanding inner works of MySQL. If you design your data wisely considering what MySQL can do and what… populate the caches. With proper application architecture and table design you can build applications operating with very large data sets based on MySQL.
