…query optimizer and see how easy it is? ;-) Generative grammars like SQL can produce an infinite variety of queries. The query optimizer analyzes queries…number…BY RAND() solution is known to have poor performance, but it returns a better random choice. Another workaround may be to add a column to…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…to the Development process a lot more effectively when I know the relationships between tables versus observing queries…how SchemaSpy identifies the relationship, and whether you need to…number of children, parents, count of columns…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
… workload consisting of a small number of concurrent queries (or only one) to demonstrate how much improvement could be made to MySQL so that is… post into a partitioned InnoDB table. Partitioning example: /*!50500 PARTITION BY RANGE COLUMNS(FlightDate) (PARTITION p2 VALUES LESS THAN (’1988-01-01…
Post: How To Test Your Upgrades - pt-upgrade
December 29, 2011 By 7 Comments
…column count, query execution time including execution errors and warnings. So how do you exactly test your queries…query log: # You can also run the slow log through pt-query-digest like the commands below # to limit the number of queries per fingerprint to…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… to note is the last line in the output the # MISC part, it tells you how much of “load” is not covered by… the query execution plan used by MySQL. The end result might be that you end up limiting the number of results returned by the query, by using a LIMIT clause or by filtering based on the option_name column, or you…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…by index is how many rows you need to scan to resolve order by (this can be found in slow query log or by…by by last column, in fact index can be used for ORDER BY if sorting is done by leading column(s). Note however columns following column…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…BY, such as this one, this creates a ranking function automatically. In cases where this is not useful, simply ignore this column…to display the total sales amount for any particular customer. Notice how you can use this summary to calculate the number…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
…how to register to attend. During my webinar, I got a number…query, by counting how many lookups to index entries or table rows will be necessary for a given query…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…Query applies a clever approach to parallelism which allows it to significantly improve the performance of queries by…by joining the newly constructed dimension table tables to the staging tables, omitting the dimension columns…by the number…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… index lookups. So we spoke about how MySQL uses the index but not …query completes in less than 10ms Now what if we pretend we only have single column indexes (by hinting optimizer to…to ? It is not combined index but single index on another column. This is because MySQL is able to estimate number…
