…MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides. Thanks to…to the same execution plan. (I know it doesn’t though.) Why don’t you try writing a SQL query optimizer and see how…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish… primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key appended to the…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
…optimized for rollbacks, it’s optimized for transactions that succeed and commit. Rollback can take an order of magnitude longer to…). But how to do …to a file; I also captured the output of “mysqladmin ext -ri5 | grep Handler_write” and logged that to a file. To… do time mysql -e “set foreign_key_…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…MySQL, you will need to aggregate your data. What you want to do is aggregate in ways that optimize… Then write your queries to use the most aggregated data possible to …have to think about how to avoid enormous tables that are hard to maintain… unsigned not null, …. primary key(day, ad, is_blue) ); …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…how to build for massive scalability, how to do read-write splitting without breaking the user experience, how to…how to read these, there’s a full explanation in our book, High Performance MySQL…optimization. Back to the output of mysqladmin. When I send the report to…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…amount of concurrent writes to about 6 tables, what version of Innodb/MySQL would you …how long will this take? Is there a facility we can run to show any table / index fragmentation, without having to run the OPTIMIZE…-hash searches/s HT: Strange, primary key equality searches should work through the…
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
…to avoid the accumulating a backlog of asynchronous writes, but writing synchronously. Although write…to be fixed. How to optimize the program without compromising data integrity is a constant question. Relative to…MySQL. My main advantage: I have been able to avoid modifying any part of MySQL to…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…how many rows you need to scan to…the tables to optimize order by,…mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table | type | possible_keys | key | key… I should write another …
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
…good for optimal MySQL Performance” is typical question I’m asked. It could by MyISAM key_…such as – how busy were drives with concurrent requests, how random were these reads/writes, how good …to do until proper instrumentation is done. In partuclar you should be looking at Key_reads and Key_writes…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…To some extent, MySQL 5.0 and later changed this situation with the introduction of the “index merge” optimizer…keys: user_type key: user_type key…optimizer by manually manipulating indexes in every query they write…
