…column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each pk_column…to the query times for MySQL 5.6, when the workload fits entirely in memory, because there is no extra cost for memory access…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… that keeps evolving, as new features are added to the application new queries pop up that need to be analyzed and fixed. And there… that logging all queries in this fashion as opposed to the general query log, enables us to have the statistics available after the query is actually… columns being accessed by the query. So a quick fix here could be to instead of selecting every column from all the tables involved in the query…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… is similar to storing the results of a query in Memcache. When…718031 | 3475 | +————-+————-+————-+ 10 rows in set (43 min 10.11 sec) As an added …In cases where this is not useful, simply ignore this column. It is used to…response times in your application by making access to…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… avoiding accessing database is the best way you can optimize database access. This applies to anything…rank query is such – adding index on rank column maxes this query to use 10 rows to return …to join all queries in single UNION (with padding to accommodate different types and number of columns) -…
Post: How number of columns affects performance ?
September 28, 2009 By 10 Comments
… access speed to different columns. the max(t1) and max(t99) were taking the same time which means there is no penalty for accessing column which is in the end of the table rather than at the start when it comes to MyISAM. The common workaround working with such wide tables is to use covering indexes. I added one to t99v1 table and repeated the query…
Post: Why you don't want to shard.
August 6, 2009 By 33 Comments
…to keep your frequently accessed data loaded exceeds what you can (economically) fit in…column to hash on, you just need to make sure that it will equally distribute the data equally. In…query first to…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…’t get access to the time of day of every click on every ad, will it hamper your ability to measure the ad‘s value… to go together. Figure out what other types of data you’ll need in those aggregate tables, and include columns to support these queries. But beware of denormalizing with character data; try to make your…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… query response times compared to accessing the non-aggregated data. Keeping MVs up-to-date …adding a table to the list of tables to changelog. It does this by adding an entry to…in the changelog, each representing one of the changes we made. You will notice that the source table only has two columns…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… column to trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query…index_creation may also optimize index access for subsequent DML statements. In my test setup I got… table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…query completes in less than 10ms Now what if we pretend we only have single column indexes (by hinting optimizer to…access through the index, which seems to be about 10x (in terms of cost access per row) even though it is in…
