…in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… cache for match. Protection from Multiple Misses So you have item invalidated from …rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in…QuickLZ. Results sets for most queries is highly compressible and light compression can compress results at …
Post: MySQL Query Cache WhiteSpace and comments
March 20, 2008 By 9 Comments
…(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql> /* comment*/ select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00… row in set (8.82 sec) Looking at these results you could judge as all problems are indeed fixed. You can have whitespace in the… query has space after comment deleted) it causes query cache miss. If we look at process list output we can see…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…page size of 16kb. this sets an ~8000 bytes limit on row size (8126 as reported by my…If you just insert dummy data in your database, you may be missing to catch important data-dependent bugs…BLOB field with COMPRESS/UNCOMPRESS can yield great results. Finally, let me mention that handling of overflow…
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
… allowed in a comma-separated string. [qc_miss, full_…set this to “full_scanâ€, while in order to get only those which use on-disk temporary storage for intermediate results…SET SESSION and SET GLOBAL. min_examined_row_limit=# Don’t log queries which examine less than min_examined_row_limit rows…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…of your workload as possible. If you miss collecting index stats while some occasional queries … garbage-in, garbage-out and you risk dropping necessary indexes. To accomplish merging multiple result sets, I…row in set (0.03 sec) Now I need a way to find the set of indexes in all_indexes, but not in…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… of Tools and Techniques for Index Design. Even if you missed the webinar, you can register for it, and you’ll… the order in which rows are accessed, so if you need to sort by another column, it must collect the interim result set and… of this interim result set is relatively small, after the GROUP BY reduces it to one row per distinct value in the grouping column…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… data corruption, crashes, or wrong query results get the highest priority. We are…: Yes, if your ‘write working set‘ fits in 16 GB. I mean, if …a time, that are esentially reads misses (10ms each). The reads are not …in an ascending order of the PRIMARY KEY, then InnoDB should have placed the rows in…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… of result sets is generally a good idea and having pre-compiled summary data ready to go in the … missing which > requires queries to do full table scans. Funny enough this is often > not the problem in … for in this table?” An index that has very high cardinality (as high as the number of rows in …
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list; 997 rows in set…data | 0.018478 | 10.70 | 3 | 0.0061593333 | | Sorting result | 0.011090 | 6.42 | 1 | 0.0110900000 | | …temporary tables. But there’s something still missing: it doesn’t show lost time (…
