…earlier post into a partitioned InnoDB table. Partitioning example: /*!50500 PARTITION BY RANGE COLUMNS(FlightDate) (PARTITION … config file. It runs the SQL via ShardQuery and it prints the results. Test #1 This set …something more like OLTP. Instead of getting one big chunk of data in one query, it …
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… tables. This results in unpredictable and therefore, undesirable, performance. The other big problem with …the entire result must be recalculated. This recalculation may be very expensive. For example, suppose … CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL commands can be used to either …
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…and-monetdb/) this week. And following Baron’s example I also run the same load against MonetDB…understand how to do that, so I generated big SQL file which contained load statements. Load of …;. Both InforBritgh and MonetDB executes it immediately with result 117023290 rows Now some random queries I …
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…and selects an extraordinarily inefficient plan resulting in horrendous performance by using one…an ORM like SQL Alchemy and want to avoid writing sql directly in application…to do so. Here’s an example table create table orders( order_id… we did it would be a big deal – requiring system down time etc….
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… respect and what works well for Oracle,MS SQL, PostgreSQL may not work well for MySQL and …example retrieving index values first and then accessing rows in sorted order can be a lot of help for big…split query into several, run in parallel and aggregate result sets. So if you’re dealing with large …
Post: The two even more fundamental performance metrics
May 5, 2011 By 6 Comments
…in most systems such as database servers. As a result, completion times shouldn’t be correlated either. But … would have actually dropped off sharply, leaving a big notch in the green curve, followed by a …have. What was the query, for example? With the text of the SQL in hand, we might be able …
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… buffer pool did not affect the results. What was happening? Well, in order…background information. My buffer pool was big enough to hold the whole database …the actual number of rows read at SQL layer is actually very different from the… a practical way, with real-life examples? Do you want to know a …
Post: Power of MySQL Storage Engines
March 5, 2007 By 3 Comments
… this type, ARCHIVE, CSV are other good examples. There are also storage engines out where …SQL layer is kind of fixed same for all. This dramatically limits what you can do. Such limit is not a big… Same applies to checking number of rows in result set and LIMIT implementation – these can optimized …
Comment: MySQL Query Cache
June 28, 2010 By 70 Comments
….cnf. You do not want to make the cache too big though, the article explains why. If you are thinking of… of it. memcache cannot cache sql results directly, but normally you would serialise the results (from PHP for example), put them in memcache, and…
