…, we have received a large number of questions on …mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… 10000 distinct value, so range 1..100 selects about 1% of the table. The times for full table scan vs range scan by index: mysql> select count(pad) from large; +————+ | count… of few large tables, which is completely disk bound can be very slow. One of the reasons elevating this problem in MySQL is…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): …enough machines, massive parallelism is possible and very large data sets may be processed. As each …
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE… for your table by simply doing: mysql> alter table mytable ENGINE=Innodb; However, this blocks and on a large table can …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select_type | table…
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
…large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table… 16 identical MyISAM files for distinct parts of the table. That was much faster… mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
…table fragmentation which affects performance of full table scan. Not all queries are going to be affected same way, for example point select…distinct…tables which could be because of extent allocation for rather large tables. For 10000 tables we had just 1000 of 4K rows in the table…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…. Test Case B – Join a small table that fits in memory to a large table with a selective WHERE clause on an indexed column… a large table The SQL used for this test together with its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT…, the joining key from the table supplier would match approximately 600 rows from the table lineitem for each distinct key value, in this…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…, because otherwise you can have a really large log file generated. Another thing that we …queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note … about the underlying tables involved and the query execution plan used by MySQL. The end result …
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
… 100 values with d=50 and some 10 distinct c values. read_buffer_size and read_rnd…you run SELECT * FROM LARGETABLE LIMIT 5 MyISAM will allocate the buffer and populate if fully when MySQL will…. For example too large table_cache may slow your opens and closes down, too large query_cache may cause…
