…to create new paths for each one, as many new paths as the number of ancestors of the new location times the number of nodes in…of itself. I also wrote about Closure Table in my book, SQL Antipatterns: Avoiding the Pitfalls of Database…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…number of tables used by transaction in question (meaning it was accessed) and number of tables locked by transactions. Innodb does not lock tables for normal operation so number of tables…number of pages in buffer pool, number of pages free, pages allocated by database…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… to update the snapshot to reflect the changes that have happened in the database, instead of…of flexibility to get the results you want, but more quickly. More on this later. SQL features used in…how you can use this summary to calculate the number of lines in the order_lines table, three orders of…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…database background. > Things are different with MySQL. Good design will help dictate how many tables are required in order to get the job done. To…to join rows together, potentially preventing a table scan. The trick is to know what kind of JOIN to use so that the right number of…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…database About the cluster For best performance, there should be an even data distribution in the system. To get…number of the flight. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
…tables with many columns (the same one Peter wrote about recently). The client’s performance is suffering in part because of the number of…of the box, for all three databases. The first thing I tried doing was loading the data with SQL statements. I wanted to see how…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…get data in chunks so it is even harder to implement. Might not work with transactions – Different transactions may see different states of the database…of usable memory – Queries are constantly being invalidated from query cache by table updates, this means number of queries in…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
…of a small number of concurrent queries (or only one) to demonstrate how much improvement could be made to…of those are not open source. In the future, Shard-Query can be extended to other database…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…to several tables or perform complex queries finding relationships between objects. Normalized structure and a lot of joins is right way to design your database…number of CPUs will not help. Sometimes it is good idea to manually split query into several, run in…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 18 Comments
…to do. You can’t fully optimize a query unless you know how to consider alternative ways to…table; If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count the number of…
