June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…query optimizer and see how easy it is?  ;-)  Generative grammars like SQL can produce an infinite variety of  queries.  The query optimizer analyzes queries for…multiple columns in a compound index, then the order would matter, and it would have to match the order of columns in…

Filed Under: Insight for Developers, MySQL, MySQL Webinars Tagged With: , , ,

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

…table built on it is greater than the join_buffer_size, then multiple hash tables would be created. For…column In this test we can clearly see that Hash Join gives a lot of reduction in query time. The reduction in query time for…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , ,

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

… table tables to the staging tables, omitting the dimension columns from the projection, instead replacing them with the dimension keys: select … multiple files (for each shard, for later loading) or it can load files directly, in parallel, to multiple hosts. Shard-Query will execute queries in…

Filed Under: MySQL

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…table changelogs which are created by FlexCDC. The refresh algorithm computes changes in multiple transactions. After each transaction it must wait for…column. It is used to prevent wide innodb primary keys on the MV. mysql> select…

Filed Under: Insight for DBAs, MySQL

Post: How To Test Your Upgrades - pt-upgrade

December 29, 2011 By 7 Comments

…SELECT queries against multiple MySQL servers and reports on how each type of query…query times. # Query 7: ID 0x76323E2525BA457C at byte 0 _______________________________ # Found 441 differences in 510 samples: # checksums 0 # column counts 0 # column types 0 # query…

Filed Under: Insight for DBAs

Post: Using Flexviews - part one, introduction to materialized views

March 23, 2011 By 3 Comments

…table. It has columns and rows, just like a database table, but it is temporary in nature, usually existing only in memory for…in time, essentially a consistent snapshot of the query result. It is not possible to create multiple different tables…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , ,

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

… rows/sec. Note multiple drives do not really help a lot as we’re speaking about single thread/query here. So difference… table with over 30 millions of rows. “val” column in this table has 10000 distinct value, so range 1..100 selects about 1% of the table. The times for full table scan vs…

Filed Under: Insight for DBAs Tagged With: ,

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

…multiple tables gives some very important benefits – your data > becomes managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for…query. For example you can do: > SELECT id FROM users WHERE featured=1; > Now populate List for IN on your PHP application: > SELECT…

Filed Under: Insight for Developers Tagged With: , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…column that you defined for the underlying table, or it can be a sequence value generated by InnoDB when the table…TABLE multiple times if we’ve had a lot of changes to the table. This all sounds pretty good, in…) SELECT reverse(full_name), details FROM dir_test_innodb WHERE id < 500000; Query OK,…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Quickly preloading Innodb tables in the buffer pool

May 1, 2008 By 9 Comments

… relatively well (though can be slow for fragmented tables) but it does not preload indexes in memory neither it does externally stored objects… such query per index is enough even if it is multiple column index. To fetch BLOB/TEXT externally stored columns you can use similar query: SELECT…

Filed Under: Insight for DBAs Tagged With: ,
« Older Posts