…recording and my slides. Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many… optimizer and see how easy it is? ;-) …in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…how InnoDB uses `PREC` please let me know. mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741; +———-+—–+————-+——-+———+——+——+ | TABLE…in CREATE TABLE statement internally they always go first in a record…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…table with a statement like this: UPDATE achievements SET count = count + 1 where achievement = ‘killed_troll’; How many…In both of the above examples you can imagine plenty of concurrent clients attempting to modify the same record…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had…: How many indexes is too many? A: There is a hard limit on amount of indexes you can have, which is 64 per table in recent MySQL versions. However it is often too many. Instead of thinking about hard limit…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite often want to know how various buffer sizes are… (key_buffer_size). There are many examples of this. What about …allocated to cache rows from each table in a join when the join … records 14178 Records are stored in tab->cache.buffer and last record in 14179 last record …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… up sending. In this case if I index the table I get the following record in the slow query log…tables but because the access type to the tables is “const” MySQL does not count it as access to two tables. In… see if query is worth optimizing ? – see how many rows query sends after group by, distinct …
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
…. As default table format for slow_log is CSV with no indexes typical operations like finding how many queries were accumulated in the logs… sort records by physical position quickly without need to have indexes would be quite handy I also should tell log table implementation in MySQL…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…row to lookup up a row in another table based on a text field ( …in memcached, I keep pushing data out of memcached to make room for new records…really solid # of hits in memcached, but the # of writes in MySQL coupled with the …the database. You would be surprised how many people use this type of …
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… rows in set (0.00 sec) After running the query, see how many pages were read from disk versus how many page requests their were: mysql…(is_old=’YES’) hot, sum(number_records) records from information_schema.innodb_buffer_page_lru where table_name like ‘%ssb%’ group by 1… As promised, here are the results from joining the tables in the other direction mysql> select straight_join sum(lo_extendedprice*lo_discount…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
…many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables… mysql -e’drop database test1′; mysql -e’create database test1′; done; tables: 1; total records: … and closing MyISAM table) can happen by OS in background and flushing…
