June 19, 2013

Post: The case for getting rid of duplicate “sets”

May 17, 2011 By 2 Comments

… data is stored inside of a histogram in the set. The cardinality and the distribution is always known. Second, computation on the compressed data… table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count(distinct id) from data where…

Filed Under: MySQL

Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By 2 Comments

…of data (just one column) and can take advantage of the covering index technique, did actually take more time to execute: mysql…of rows read at SQL layer is actually very different from the “rows” value seen above. This is because the second part of…

Filed Under: Events and Announcements, Insight for Developers, MySQL Tagged With: , ,

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…) Data dictionary Here is a quick example of the list of tables used by the above view, as stored in the data dictionary: mysql> select…: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’, -> ‘dashboard_customer_sales’),’BOTH’,NULL); Query OK, 0 rows affected (7.01 sec) The second one…

Filed Under: Insight for DBAs, MySQL

Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1

October 15, 2009 By 16 Comments

… that key.  It then uses data from that random row to look up a second piece of information via email address.  Because… am still getting a really solid # of hits in memcached, but the # of writes in MySQL coupled with the still large # of reads takes its… via that key. It then uses data from that random row to look up a second piece of information via email address. Because the…

Filed Under: Benchmarks, Cloud and NoSQL, MySQL Tagged With: , ,

Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster

March 3, 2013 By 13 Comments

… to MySQL replication latency — or better yet, call it data propagation latency. It was interesting to see whenever I can get stale data reads… times faster still taking tens of seconds for what I would consider medium size transaction modifying 1 million of rows is rather long time…

Filed Under: Benchmarks, Insight for DBAs, MySQL, XtraDB Cluster Tagged With: , ,

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

… inner works of MySQL. If you design your data wisely considering what MySQL can do and what it can’t you will get great perfomance… can get 100MB/sec read speed which gives us about 1.000.000 rows per second for fully sequential access, jam packed rows – quite…’ll need to perform 30 millions of random row reads, which gives us 300.000 seconds with 100 rows/sec rate. So we would…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…getting it configured. The second part will compare query results between MyISAM FTS and InnoDB FTS over the same data…of my DIR data. So, with all of that out of the way, let’s get …rows in set (0.00 sec) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: How fast can MySQL Process Data

April 9, 2008 By 3 Comments

…data processing limitations of current systems. This raises valid question how fast can MySQL process (filter) data…rows per second it will be able to do? CREATE TABLE `m` ( `i` int(11) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 mysql…

Filed Under: Benchmarks Tagged With:

Post: How fast can you sort data with MySQL ?

August 18, 2007 By 26 Comments

… table scan of the table completes in 0.22 seconds giving us about 4.5 Million of rows/sec. Obviously we can’t get sorted… | 81980bcd9dbaa565f22a93ce1faf9e9d53407f0a | +——–+——————————————+ 10 rows in set (0.56 sec) Not bad ! Even though MySQL does not optimize “get top N sorted rows” very well it… get the data. And this is with minimum sort_buffer allowed when a lot of sort merge passes are required for sort completion: mysql…

Filed Under: Benchmarks, Insight for DBAs Tagged With:

Post: MySQL EXPLAIN limits and errors.

July 24, 2006 By 6 Comments

… cost of the operation – full table scan of table in memory may be done at speed of millions of rows per second, while retrieval of large rows from… the data, methods used, costs etc. Simple table like output format is good but with growing MySQL Server complexity it is getting impossible…

Filed Under: Insight for Developers Tagged With: ,
« Older Posts
Newer Posts »