… collation specific. Q: ORDER By optimization issues: select * from table where A=xxx and B between 100 and 200 order by B Very common… index on the field that is used to join the two tables? A: The question in this case is how MySQL will execute… order by on another column. do i need to add the index on column using order by clause. A: If index is used for ORDER BY…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…ORDER BY … LIMIT you should try hard to have sorting column(s) to be in the leading table. If ORDER BY is going by field…ORDER BY in other table. Here is example when ORDER BY is done by second table which requires filesort: mysql…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… filtered on, and there are also bunch of fields which sorting can be performed on such as seller… of restrictions and order by to be fully indexed. An extra problem comes from the fact MySQL prefers when … two example queries is about 100 times so it may be quite serious. To fix this issue MySQL …
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state… to be data we had available for tests) Two important notes about this table before we get…mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c.state_id) ORDER BY…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… type of index merge algorithm in the “Extra” field. Here’s an example of a table and … the most commonly executed query on the server by two orders of magnitude. Other queries were running maybe 1000 … AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users …
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
…order and it will well be. This is how MySQL will optimize indexed ORDER BY…order by two colums in different directions: … ORDER BY price ASC, date DESC LIMIT 10 If you have indexed on (price,date) in ascending order…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… solution: SELECT update_time FROM foo ORDER BY update_time ORDER BY update_time LIMIT 3; And …field first (so that it would be treated explicitly as a string) produces the 0024-06-22 result. The documentation for MySQL…hand, it seems like MySQL is using date windowing for two-digit dates (this would…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_template_rrd data_input_fields # 16 0xBE797E1DD5E4222F 0.0524 0…_one (264/84%), wp_blog_two (36/11%)… 1 more # Query_time distribution …by MySQL. The end result might be that you end up limiting the number of results returned by the query, by…’) AND tr.object_id IN (733) ORDER BY t.name ASC\G Let’…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…space if the fields are less that 16 kB. The ‘zip’ source code tree by Marko has removed…kernel can decide (incorrectly) to swap out MySQL. There are two solutions here. Memlock and O_DIRECT. …MySQL‘s binlog and InnoDB’s log must have the transactions in the same order, for a recovery based on MySQL…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… table from a full backup in order to save time and make … to identify which of those two indexes is the Primary Key. …mysql (employees) > LOAD DATA INFILE ‘/root/recovery-tool/data/salaries.recovery’ REPLACE INTO TABLE `salaries` FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY…
