…by few rows. mysql> select * from node procedure analyse() \G *************************** 1. row *************************** Field_name: …significant the change may make sense. Another important piece of information for …example if column always have same width like hashes CHAR can be more efficient…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…the type of index merge algorithm in the “Extra” field. Here’s an example of a table and …the best way to go for a permanent solution. Another option is, of course, to change the indexing …one that MySQL should be looking at, we can rewrite the query like so, with the following result: SELECT user_id…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…_id column. There will be another blog post about the usage… to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’G …field is selected in the list of columns to show (click show/hide to change the columns) That the “public dns” field is selected…
Post: Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
… the MySQL protocol level, which will increment Com_admin_commands in SHOW GLOBAL STATUS, or a trivial query such as ‘SELECT 1… in the field, until performance really starts to matter — and then it sticks out like a sore thumb. (Edit) Here’s another example… COMMIT # 6 0xA873BBC4583C4C85 278.4533 1.9% 6985 0.0399 SELECT users user_devices That’s right, 73% of the server…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… leading table. If ORDER BY is going by field from the table which is not first in…likely selected even if it leads to filesort. The solution for this problem is ether extending your indexes so MySQL… but there are some differences. I should write another article about ORDER BY without limit and large…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… on, and there are also bunch of fields which sorting can be performed on such as…MySQL can stop as soon as 10 rows are sent. In this case with seller_id being not really selective it is likely…complex searches which generate a lot of rows. Another interesting technique is to use sphinx search …
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
…bloated and slow. Even simple SELECT queries might have to …how you make InnoDB behave like PostgreSQL 
If the undo …-clone the machine from another, which takes about 10 ….txt’ into table load_test fields terminated by ‘\t’ lines terminated by …-e /tmp/my-fifo ]; do time mysql -e “set foreign_key_…
Comment: Innodb vs MySQL index counts
November 30, 2011 By 6 Comments
… in ‘field list’ > insert into test( id, data ) VALUES (3, 1001 ); Query OK, 1 row affected (0.01 sec) > select * from test… No errors in the mysqld.err. (this was on vanilla MySQL 5.5) Let’s see if the ALTER TABLE …rows in set (0.00 sec) Yup, seems to. Seems like this is another blog post by itself.
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… of a lot of questions at MySQL Performance Forum as well as from … which do group by one field sort by another and then use LIMIT to … profile browsing at social networking site – SELECT COUNT(*) FROM profiles where STATE_ID=…If the number of combinations is small, like in this example you can use …
Post: Fishing with dynamite, brought to you by the randgen and dbqp
November 16, 2011 By Leave a Comment
…bugs. Of course, I would also like to think that the way …test: ./dbqp.py –default-server-type=mysql –basedir=/path/to/Percona-Server –…possible WHERE clause for SELECT queries: WHERE X . char_field_name != ‘If you … test case file + modifications resulted in another easily reproduced crash. I will …
