…tables may take a lot of time. Fast Index creation will allow Innodb to build indexes by sorting rather by row by row…performance benefits in MySQL 5.1 It also should be possible to start unlocking rows…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…table has just couple of rows…MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql…scan was initiated. Handler_read_next=42250 means 42250 rows were analyzed during this scan. Basically MySQL started scanning Index with age>=18 and continue scanning…time…
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
…IO bound full table scan and you have 100 rows per page you will get 1 miss per page and 99…some 1.000.000 rows per second. And at the same time you have some performance critical transaction which …it is unlikely the problem for MySQL Performance. If it is however 120 per second it well could be…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
…DEFAULT CHARSET=utf8 This table contained some 25 millions of rows and no indexes…MySQL these days. Now on performance – for given class of queries Sphinx was just 1.5-2 times…rows per second (this is on rather outdated CPU I used for testing) – this means you should be able to scan through 100M+ rows…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…not any more performance can degrade 10 or more times. Q: …, which is 64 per table in recent MySQL versions. However it is… an analyze on a table with 11M rows I’ve seen …MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL…
Post: Is your server's performance about to degrade?
May 18, 2010 By 13 Comments
…performance…time: 0.306486 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows…Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 0 # administrator command: Quit; Suddenly these queries, which don’t touch any tables…MySQL…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 16 Comments
… convinced MySQL to perform the query as a table scan of the fact table, then an index lookup in each dimension table in turn. The table structures… the server was reading roughly 43K rows per second, and there were 150 million rows in the table. A little math later, and you…
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
…id as unsigned int. As the time goes and you see you may…capacity to handle millions of comments per item. Note the question in …column) it truncates the value, performs index lookup, scanning all rows (just 3 in my … mysql> set sql_mode=STRICT_ALL_TABLES; Query OK, 0 rows affected (0.00 sec) mysql>…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
MySQL Server has tons of variables which may be adjusted to change behavior or for performance…time Flushing (closing) of tables after query is complete or every flush_time seconds. This is technically variable which affects all tables…tables for Full Table Scan (read_buffer) and for reading rows…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…table will perform complex operations such as full table scans, sorts, or need temporary tables… per thread buffers unless you have complex queries. Sorting 10 rows…tables. It also means user having CREATE TABLE privilege should be able to run MySQL…. Blobs may require 3x time of memory. This is …
