… of rows under explain. Using innodb. Inserting up to 100 per second. Query select g.date_time, g.speed, g.odometer, gl.building_number…_idle, m.id as is_moving, o.id as over_speed, l.name as location_name from gps_positions g inner…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
…affected same way, for example point select reading single page should not be …inserts going to random tables. I used default MySQL settings for MyISAM (table_cache=64) and set innodb_buffer_pool_size=8G innodb…configuration. Going to 10000 tables however insert speed dropped 20 times. This could …
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… to note Innodb (which is storage engine which usually considered best for parallel insert/updates) has … to Innodb tables aspect of primary key selection – you often can gain a lot by selecting primary … benchmarks was rather lookup by primary key speed. Lookup speed can be similar or can vary a…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… optimizer knowing index is UNIQUE the drawback is insert buffer will not be able to be used, …my query is only selecting on vendor, will the index have any impact on the speed of the query? A…for “order by desc” indicates a double linked list. Innodb has double linked list – each leaf page contains …
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
…speed up flushing we can set dirty pages percentage to zero: mysql> set global innodb…never less than 1): ————————————- INSERT BUFFER AND ADAPTIVE HASH INDEX …restart all InnoDB tables are reachable: mysql> select count(*) from sbtest; +———-+ | count(*) | +———-+ |…
Comment: Benchmarks of new innodb_flush_neighbor_pages
July 3, 2012 By 8 Comments
… Partition Selection, Split Kernel Mutex, Multi-Threaded Purge, Separate Flush Thread, Pruning the InnoDB Table Cache This article was for scaling inserts so I dont know if it will help for Selects and Updates… not ram for mysql like varnish for http webserver should speed up mysql since its already technically on the disk. Also…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
…) 1500M. See http://mysql.rjweb.org/doc.php/memory For InnoDB, innodb_buffer_pool_size = 5500M might be optimal. (Plus key_buffer…(s)”, populating them as the data arrives, then SELECTing against them. The 3rd SELECT may work better in newer versions of MySQL… the _only_ SELECTs you need, then DROP all your secondary keys; they are possibly useless. This will greatly speed up INSERTs. > PRIMARY KEY…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
…, when testing the InnoDB FT index, I made sure to increase innodb_buffer_pool_size to…indexes stay up to date with changes (inserts, updates and deletes)? That’s a …, and if you can form a SELECT query that returns the “new” data (…, you can get a lot of speed improvement because you’re searching data…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…expect it to beat MySQLi in speed. It is however bad idea to …increment column) in nearly (emphasize nearly) every InnoDB table can have very serious … may add processing time to inserts, updates, and deletes but there are …) > instead of: > SELECT * FROM articles WHERE user_id IN (SELECT id FROM …
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
…have already started evaluating InnoDb for this…) I…cores 3 GHz. high speed Raid level 1 disks…T, ObjectInstances_Week8_T) INSERT_METHOD=FIRST; (SEVEN … SELECTS are ( replaced actual fields with “*” ): ##————————————————– SELECT o1.* FROM ObjectInstances_T o1 ,(SELECT…
