… have LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len…
Post: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 21 Comments
… is the third in a series on what’s seriously limiting MySQL in certain circumstances (links: part 1, 2). This post…. The following query will surprise users unpleasantly: select * from a where a.id in (select id from b); Users expect the inner… de-optimizes the query in many other cases. NOT IN(SELECT …) queries execute badly, too. (Note: putting a literal list of…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL… be the right solution because our SELECT will only be faster when the condition is very selective and the buffer pool is… it an edge case — but I would call it a limitation of the current query planner. However I would like to…
Comment: Database access Optimization in Web Applications.
September 17, 2006 By 10 Comments
Pedantic, There are cases when SELECT * FROM table WHERE has limited overhead. There is always overhead (unless you list all fields) … columns: – Query can be index covered if you select limited number of columns. – Selecting all columns may require more network traffic and more… and use only columns you need. There are other reasons SELECT * is bad practice besides performance – for example reffering to columns…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… y BETWEEN 50 AND 60 AND col_a = ‘set1′; +—-+————-+————-+——-+—————+———–+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…_y_col_a) WHERE x BETWEEN 30 and 40; +—-+————-+————-+——-+—————+———–+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
February 6, 2009 By 14 Comments
… records. I mean, about 1000 times. (SELECT …….. LIMIT 15) UNION ALL (SELECT ……… LIMIT 15) . . ORDER BY CREATED LIMIT 15 Or do you advice me to…
Comment: How To Test Your Upgrades - pt-upgrade
July 24, 2012 By 7 Comments
… and UPDATE, so I assumed as its definition (–convert-to-select) its working fine but I might be wrong then. So….1.100,u=perc,p=perc,P=3306 –convert-to-select –limit 100% –temp-database pt_upgrade –clear-warnings-table pt_upgrade…,warnings –compare-results-method rows, Should I use –convert-to-select in the above pt-upgrade query? I have same set…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…_KEY_POINT_LIMIT Query: SELECT name FROM $tableName WHERE country_id = %d LIMIT 5 The same query as previous but with LIMIT clause. Due…_NO_DATA_LIMIT Query: SELECT state_id FROM $tableName WHERE country_id = %d LIMIT 5 The previous query but with LIMIT. Again the LIMIT is…_DATA_LIMIT Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50 READ_FTS Query: SELECT…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… by data limit 78000,1;select * from sorttest order by data limit 78000,1;select * from sorttest order by data limit 78000,1;select * from sorttest order by data limit 78000,1;select * from sorttest…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… VARCHAR: select SQL_NO_CACHE city from cities_varchar ORDER BY state limit 10000, 5; Result time(mean): 0.0854793 3) select SQL…_enum limit 10000, 5; Result time(mean): 0.003125 2) Results for VARCHAR: select SQL_NO_CACHE city, state from cities_varchar limit 10000, 5; Result time(mean): 0.003283 3) select SQL_NO…
