…greater than the join_buffer_size, then multiple hash tables would be created. For …WHERE clause on an indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL…with no WHERE clause, or a WHERE clause on a non-indexed column. They also provide big improvement in …
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…multiple threads and multiple concurrent transactions ? HT: The scalability in…where clause when it was not resolved by index HT: That is already implemented in…columns in an index, in the same order in…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… developed, but if we stick to simple MySQL using multiple indexes on most selective columns would be good idea for performance of such… to be adjusted in the future. The main thing to watch for, if you do not have full where clause resolved by index is how many rows you need to scan to resolve order by (this can be found in…
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 141 Comments
…columns in other tables, and the small letters are just data columns that are returned but never used as filters (WHERE/JOIN/ORDER CLAUSEs…in the system, and preform simple SELECTs in them (not joining them with other tables). Once again, I use multiple…
Comment: MySQL VIEW as performance troublemaker
May 6, 2010 By 57 Comments
mysql view are good, but certain care is required – 1. tables … data in the tables) 2. skip any where clause, or group by clause 3. drop off any columns which you dont require, or create multiple views, based on the column required 4…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… especially inefficient with Innodb tables both in terms of space (some tables … can simply rewrite software to store multiple users per table it is best… posts and contains id, title, body columns (to keep it simple). There …views. You could think MySQL would look at the WHERE clause to figure out …
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… fraction of queries. If you’re joining multiple tables or if you have GROUP BY …WHERE ID%100=0 you can have extra column divisible_by_hundred and keep it indexed. Of course in…WHERE COUNTRY=’USA’ As of MySQL 5.0 MySQL will materialize the subquery in the from clause fully and so “use” all rows in…
