…find bits of time from processing accumulated backlog. The Theme of this Users Conference was surely Storage Engines both looking at number of…In MySQL 5.1+ it is also possible to fix this problem for cases when number of rows in…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5….number of rows in millions and data and index size in GB so I can save on counting zeros. The last column shows how much does the…the query so it works whatever database you have active. It does not work with MySQL before 5.0 still of…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
…tables Amount of rows used was 20M, transactions were not used (autocommit), number of threads – 1, 4, 8, 16 and 32. Benchmark scenarios Here’s the…in case of 8 threads running concurrently. Because the customer was running a database which was way bigger than 16G, I’ve repeated the…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 18 Comments
…table; If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count the number of values. Sometimes, but not always, these are the same thing. COUNT(*) always counts the number of rows in the…
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
…Find total number of tables, rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows…row in set (0.03 sec) Find biggest databases SELECT count(*) tables, table_schema,concat(round(sum(table_rows)/1000000,2),’M') rows…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
…rows in set (0.00 sec) mysql> SHOW INDEX_STATISTICS LIKE ‘hosting2%’; +——————————————–+———–+ | Index | Rows…the number of queries issued or any other information find active tables which do not have any indexes used meaning table…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…mysql> select count(*) from dim_airport; +———-+ | count(*) | +———-+ | 396 | +———-+ 1 row in set (0.00 sec) The final dimension is the flight dimension, which contains the flight numbers…
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…the mailing lists to find the correct COPY syntax — it wasn’t that listed in the…database use — there are a number of…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… columns should come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but… ignore those for the sake of simplicity. Let’s start with this query, which returns zero rows but does a full table scan. EXPLAIN…
Post: Data mart or data warehouse?
July 15, 2010 By 13 Comments
…row as the item information. It would be possible create two different dimensions, product and category, but performance tends to decrease as the number of…
