… the key value and repeating it then repeating it for left or right half. For compressed block this is not going… over to get previous value Random single row lookup by index (ie joins) will also suffer as key block needs to be… to traverse large memory areas. Reverse index scan performance got some 8 times faster and join on string value is almost 3…
Comment: ORDER BY ... LIMIT Performance Optimization
July 9, 2007 By 100 Comments
… page and sort by a few fields – i have added indexes for all the used cases).. the 100 is just the… results: SELECT users.username,bla bla….. FROM whatever LEFT JOIN categories ON category = categories.id LEFT JOIN users ON torrents.owner = users.id WHERE…,1 SELECT users.username,bla bla FROM whatever LEFT JOIN categories ON category = categories.id LEFT JOIN users ON owner = users.id WHERE visible…
Comment: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
July 7, 2008 By 30 Comments
… tried indexing state_id on the cities_join table. though I read something about frequent inserts with indexes being slow. but would indexing speed things up? and also SELECT SQL_NO_CACHE c.city FROM states s LEFT JOIN cities_join… searching by state maybe if you place it at the left mysql would search for that first and then search for…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… if I want to use index hint. [USE INDEX FOR ORDER BY (idx_name)] when we are using Joins in the query. For Ex:- Select a.test_id, b.test_data, c.test_details From test a left join test_data b on a.test_id = b.test_id left join test_details c on b…
Comment: Quickly finding unused indexes (and estimating their size)
December 6, 2012 By 2 Comments
… 5GB table and you are attempting to remove 500MB in indexes, you’lll still need at least an additional 4.5GB… wish tutorials would make more use of NOT EXISTS vs LEFT JOIN NULL. I believe it is slightly more readable in that… in the other. Also, MySQL is likely to optimize that “left join null” using a “not exists” behind the scenes anyway.
Comment: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
@Kenn Ejima ，I meet with you the same question。 @Miguel Question on the “Duplicate keys on composite indexes” section: i meet with inner(left) join on only one join condition（int or varchar data type），i found only use single-column index but not leftmost prefix index from explain output sometimes
Comment: Why Index could refuse to work ?
June 15, 2009 By 15 Comments
Hi, any other reasons why FORCE INDEX is ignored? I have a table with 100 columns which … have indexes on them. A varchar column is being used as to left join to another table which also has an (btree) index on…
Comment: Is there a performance difference between JOIN and WHERE?
January 17, 2013 By 30 Comments
… query you give could be equivalently stated: SELECT * FROM table1 LEFT JOIN table2 ON (table2.x, table2.b) = (table1.y, table1.a… help readability, and can help indicate good places for compound indexes.
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…| Primary | | wsrep_connected | ON | | wsrep_local_index | 1 | | wsrep_ready | ON | only wsrep_local_index defers as expected. Now let’s …11e1-0800-2491595e99bb,2) memb { 86928728-d56d-11e1-0800-f7c4916d8330, } joined { } left { } partitioned { 7e6d285b-d56d-11e1-0800-2491595e99bb, }) 120724 10:58:09…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… Tables views which you join together you may have serious issues because such tables do not get any indexes. Let me now… users comments to the blog, naturally containing user_id which left comment, comment_id and comment text: CREATE TABLE `comments` ( `user… create a very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt…
