…_junk(author), ltrim_junk(title) from paintings limit 100000; 100000 rows in set (0.13 …count(*) | +———-+ | 101533 | +———-+ 1 row in set (6.82 sec) mysql> select count(*) from paintings where author != ltrim_junk(author); +———-+ | count(*) | +———-+ | 28335 | +———-+ 1…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…_DATA_LIMIT Query: SELECT state_id FROM $tableName WHERE country_id = %d LIMIT 5 The previous query but with LIMIT. Again the LIMIT is… times and than Falcon by 1.64 – 3.85 times. READ_PK_RANGE_INDEX Query: SELECT count(id) FROM $tableName WHERE id… and state_id between %d and %d LIMIT 50 Again Falcon does not hanle LIMIT and the results are much worse. READ…
Post: Gathering queries from a server with Maatkit and tcpdump
July 1, 2009 By 23 Comments
… second? We can find out. mk-query-digest –type=tcpdump –limit 1 tcpdump.out # 460ms user time, 30ms system time, 8.88M… –limit. # pct total min max avg 95% stddev median # Count 12 1 # Exec time 88 101ms 101ms 101ms 101ms 101ms 0 101ms # Users 1 msandbox # Hosts 1 127.0.0.1 # Databases 1 # Time…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…is about beating data processing limitations of current systems. This …count(*) from m3; +———-+ | count(*) | +———-+ | 10476840 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from m3 where i>0; +———-+ | count(*) | +———-+ | 3492290 | +———-+ 1 row in set (1…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
I wanted to share a little rule of thumb I sometimes use to decide which 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 I will also ignore those for the sake of …
Comment: Four ways to optimize paginated displays
September 25, 2008 By 27 Comments
… ago. 1) SQL_COUNT_FOUND_ROWS can make your query much, much more expensive. Consider: select * from log order by timestamp limit 1,10… does his strategy is flawed). 3) On myisam, high offset limit clauses are horribly slow e.g. select * from log order…, very slow (Innodb is much faster at these high offset limit clauses, but still slower than a low offset). If the…
Comment: ORDER BY ... LIMIT Performance Optimization
December 29, 2008 By 100 Comments
… from your post: # select photo.id from photo where category=1 limit 30000000,20 # Query time: 0.030 (this just uses the… a,b from c; So I have: > select count(*) from c; +———-+ | count(*) | +———-+ | 20971520 | +———-+ 1 row in set (0.00 sec) > show index from… | 1 | id | A | 20971520 | NULL | NULL | | BTREE | | +——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ 1 row in set (0.00 sec) And finally: > explain select id from c limit 20000000…
Comment: Finding out largest tables on MySQL Server
October 6, 2008 By 27 Comments
…length+index_length DESC LIMIT 10; +———————————————-+——–+——-+——-+————+———+ | concat(table_schema,’.',table_name) | …1 row in set (0.02 sec) mysql> select count(*) from table; +———-+ | count(*) | +———-+ | 14828945 | +———-+ 1…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row in set (0.00 sec) mysql… docs, the number of threads that can enter InnoDB is limited by innodb_thread_concurrency (which is why these two variables…
Post: Figuring out what limits MySQL Replication
March 7, 2007 By Leave a Comment
… which highlighted replication could be the limiting factor for this system quite soon… to get most common queries: ./mysqlsla-1.4 –top 100 –raw q1.log …, 72740 unique Sorting by ‘c’ __ 001 _______________________________________________________________________ Count : 114329 (2%) UPDATE mlp_rating….
