June 20, 2013

Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 2

May 30, 2013 By 6 Comments

…> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ # s1 is behind s1> select count(*) from t; +———-+ | count(*) | +———-+ | 0 | +———-+ Can we still use master_auto_position = 1? Let… fixed, it is good to be aware of the current limitations.

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

Post: More on MySQL transaction descriptors optimization

April 26, 2013 By Leave a Comment

… mutex protecting the list of transactions, its applicability is quite limited. Namely, it requires the list of transactions to be either….lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval=1 –rand-type=uniform –forced-shutdown=1 –max-time….lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval=1 –rand-type=uniform –forced-shutdown=1 –max-time…

Filed Under: Benchmarks, MySQL Tagged With: ,

Post: Understanding the maximum number of columns in a MySQL table

April 8, 2013 By 5 Comments

… > 65535L || int_count > 255) { my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0)); DBUG_RETURN(1); } Which is, of course, obvious! Various parts of this limit are: info_length is roughly 2… limit of 1,000 columns. This is the code from ha_innodb.cc that enforces that limit: if (form->s->fields > 1000) { /* The limit…

Filed Under: MySQL Tagged With: , , , , , , , ,

Post: Is Synchronous Replication right for your app?

May 14, 2013 By 18 Comments

… this table with a statement like this: UPDATE achievements SET count = count + 1 where achievement = ‘killed_troll’; How many players might accomplish this… specified by this variable (default is 1 retry).  This means more waiting, and after the limit is exceeded you will still get… Example 1, above), how about maintaining the counter in memcache or redis and only flushing to the database periodically? if( $last_count…

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With: , , , , , ,

Comment: Using index for ORDER BY vs restricting number of rows.

April 11, 2013 By 8 Comments

… trouble。。。。 the table: ID my_id follow 1 1 16 2 1 15 3 1 14 4 1 14 I just want to find out The highest number of follow and order by follow。 I wrote: SELECT follow,count(*) AS NUM FROM wp_fans GROUP BY follow order by NUM desc limit 5 Follow was index…

Filed Under: Insight for Developers Tagged With:

Post: Using delayed JOIN to optimize count(*) and LIMIT queries

April 6, 2007 By 22 Comments

… MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval …select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1…

Filed Under: Insight for Developers Tagged With: ,

Post: Getting around optimizer limitations with an IN() list

January 9, 2010 By 9 Comments

… AND 60 AND col_a = ‘set1′; +———-+ | count(*) | +———-+ | 1 | +———-+ 1 row in set (0.00 sec) SELECT count(*) FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 and 40; +———-+ | count(*) | +———-+ | 1664 | +———-+ 1 row in set (0.01 sec) Did you notice that…

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

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

February 26, 2013 By 1 Comment

… 16:09 FTS_00000000000000ad_STOPWORDS.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15:54 FTS_… | +—————————+———+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1028261 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | …

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

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

…count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.69 sec) mysql> explain select count… id desc limit 1; +———+—–+—+ | id | a | b | +———+—–+—+ | 3000000 | 100 | 7 | +———+—–+—+ 1 row in set (1.01 sec)…

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

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

…the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row in set (0…AND Year BETWEEN 2000 AND 2008 GROUP BY 1 ORDER BY c LIMIT 10; The next queries show how …

Filed Under: MySQL
« Older Posts