…update but you also can implement all updates from the chunk in single…table” trick – use two tables insert into one and process and truncate another. MySQL offers atomic RENAME TABLE call which can be used to swap two tables…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
…in two basic flavors. Some servers have specialized data stores which store data in…tables updated and in the complexity of the many scripts or jobs which might be necessary to keep the tables in…queries or drag and drop a report together in… in a single …MySQL performance blog, and as such an in…
Post: Managing Slave Lag with MySQL Replication
October 12, 2007 By 15 Comments
…update queries are short. If you need to update 1.000.000 rows you do not do it in the same query…BIN_LOG=0 before you run ALTER TABLE query. Replication Overload Many tutorials out where …- because MySQL Replication runs in single thread (two threads, but only one of them executes the query) it is…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
…query, and if you can form a SELECT query that returns the “new” data (for example, WHERE updated…table-scans. Jessy B. also asked: Q: Were these tests performed on a single…me two to three weeks to do something equivalent with MySQL … had in common, so I tested only simple queries without customizing …
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
…queries on a single…updated: 240 Select_commands: 1759 Update_commands: 254 Other_commands: 85 Commit_transactions: 0 Rollback_transactions: 0 mysql> SHOW TABLE_STATISTICS LIKE ‘hosting2%’; +——————————–+———–+————–+————————-+ | Table…in MySQL…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
…tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in…Queries like INSERT … ON DUPLICATE KEY UPDATE…table is a clever trick that allows joining tables while keeping them independent of each other in a query. This property allows interesting queries…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
…simply run two queries: mysql> select grp, …in temporary table and run sum() and sort query on that table…in set (28.79 sec) As you can see there is considerable penalty associalted with GROUP BY WITH ROLLUP in MySQL…single query ? Sure. Here is Way Number Three: mysql>…recorded events. UPDATE: Looking …
Post: Read Buffers, mmap, malloc and MySQL Performance
September 12, 2007 By 8 Comments
…single user information ? Something is fishy here and perhaps bug needs to be filed either resulting in fixes or docummentation updates…in the pool you could just grab one having allocation very quick. Also MySQL should get smarter in…table or LIMIT 10 in full table scan query…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row in set (0.00 sec) mysql> UPDATE test_table SET id=4 WHERE id=1; — 4 Tickets Used (because no index, a table scan is performed) Query OK, 1… this information to us. What happens in the case where I have two distinct access patterns: single row primary-key lookups and SELECT…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL…query hacking and we get a query which will return statements to initialize key buffers according to table sizes and activity (in…
