June 18, 2013

Post: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

…. DVD Store was special type of application which was designed to have very simple logic besides database – in most cases you would have… hand in MySQL you might be better of using several queries than doing complicated ones. Of course you would rather use IN() than do 100 of queries in this…

Filed Under: Insight for Developers Tagged With: , ,

Post: Data compression in InnoDB for text and blob fields

May 30, 2012 By 2 Comments

… compress only certain types of columns in a table while leaving other columns uncompressed?… same value of the InnoDB page size of 16KB?  As the fine MySQL manual states: …in queries against this table when the TEXT fields were not part of the query request due to Barracuda  not storing 768 bytes of…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Power of MySQL Storage Engines

March 5, 2007 By 3 Comments

… paths dramatically. I know number of projects being interested in special techniques applying to optimizes certain types of queries with MySQL, it is not easy to… through hacks. In any case this is very interesting how it will be evolving and how more parts in MySQL will become pluggable…

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

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… for certain types of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of the… when MySQL is doing this; run an EXPLAIN on your SELECT and you’ll see “index_merge” as the type of query and the type of index merge algorithm in the “Extra” field. Here’s an example of a table and a query which I…

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

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

April 4, 2011 By 20 Comments

… view is refreshed. This is similar to storing the results of a query in Memcache. When the cache “expires”, the contents must be… that the minimum amount of drift for this type of MV is the amount of time it takes to execute the query on which the…. Both of these views build in about the same about of time: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'complete_example2′)); Query OK, 0…

Filed Under: Insight for DBAs, MySQL

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… them in.  Listen to some talks by 37Signals if you need inspiration to toss things out.  Define the types of queries you absolutely have to have, if possible, and note the ways and types of aggregation (by-ad by… of data processing in MySQL, you’re going to end up heavily I/O bound.  Listen to any of the talks at past MySQL…

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

Post: How to find MySQL queries worth optimizing ?

September 11, 2012 By 6 Comments

…; SET timestamp=1347397784; select * from sbtest where pad=’abc’; The query in this case has sent zero rows (as there are no… because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of “real” access… scanning 10 million, while we can’t really optimize this query in a simple way because scanning all that rows are actually…

Filed Under: Insight for DBAs, MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and… in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL… what are the good and bad query execution plans. a. Bad Plan id select_type table type possible_keys key key_len ref…

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

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

February 26, 2013 By 1 Comment

…in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically identical to MyISAM full-text search, in…Query OK, 200000 rows affected (8.65 sec) mysql> optimize table dir_test_innodb; +———————-+———-+———-+———-+ | Table | Op | Msg_type… number of code changes. In part 2 of this …

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

Post: Getting around optimizer limitations with an IN() list

January 9, 2010 By 9 Comments

… wouldn’t scale. To understand why these sorts of geographic search queries are problematic in MySQL, it’s best to show some execution plans… only on a small amount of data (16 000 rows) that fitted in memory, but the original query would have full table scanned… difficult to maintain. If you really want to do these types of queries, you should give Sphinx a try.

Filed Under: Insight for Developers, MySQL Tagged With: , , , , , ,
« Older Posts
Newer Posts »