June 19, 2013

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…difference between innodb_buffer_pool_stats side by side: INNODB_OLD_BLOCKS_TIME=0 INNODB_OLD_BLOCKS_TIME=1000 *************************** 1. row ************************************************ 1. row…

Filed Under: MySQL Tagged With: , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows affected (0… rebuild. Calculation of match score is completely different between the two engines; sometimes this leads to wildly different results. If you were hoping to…

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

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

April 4, 2011 By 20 Comments

… thing to know right now is that there are two significant differences between the methods. A view which uses the complete… than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…

Filed Under: Insight for DBAs, MySQL

Post: The MySQL optimizer, the OS cache, and sequential versus random I/O

April 28, 2008 By 17 Comments

… 1. row *************************** Name: fact Engine: MyISAM Rows: 147045493 Avg_row_length: 117 Data_length: 17217646764 Index_length: 11993816064 mysql>…). The difference between the two queries — 55 minutes and 2.6 days — is basically the difference between scanning data…

Filed Under: Insight for Developers Tagged With: ,

Post: InnoDB's gap locks

March 27, 2012 By 15 Comments

… in a running transaction, two identical statements get different values, because some other transaction has modified the table’s rows. For example: transaction1… lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700… constraint checking or duplicate-key checking. The most important difference between these two options is that the second one is a global variable…

Filed Under: MySQL

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… you need. Because I’ve built two such systems to manage online ads …Requirements The words “need” and “want” are different.  Do you really need atomic-…s easy to flip back and forth between viewpoints on this and get distracted … queries that read a lot of rows — MySQL can’t handle too many of those…

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

Post: Data mart or data warehouse?

July 15, 2010 By 14 Comments

…analysis. Part 1 – Intro to OLAP Identifying the differences between a data warehouse and a data mart. (this… in the same row as the item information. It would be possible create two different dimensions, product and… it may be on MySQL, but it is definitely possibly to use MySQL as long as …

Filed Under: Insight for Developers, MySQL

Post: Using index for ORDER BY vs restricting number of rows.

February 16, 2007 By 8 Comments

… interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for… will need to scan less than 100 rows to generate result. The speed difference between these two example queries is about 100 times so it may be quite serious. To fix this issue MySQL would need…

Filed Under: Insight for Developers Tagged With:

Post: Using UNION to implement loose index scan in MySQL

August 10, 2006 By 15 Comments

… Lets see how query times differ in these cases: mysql> SELECT sql_no_cache… | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 1 row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN…gender instead of age with just two possible values it would be …

Filed Under: Insight for Developers Tagged With: ,

Post: Aligning IO on a hard disk RAID – the Benchmarks

June 9, 2011 By 13 Comments

…different IO alignment, not different MySQL configurations, I didn’t try out different MySQL…grant-tables Amount of rows used was 20M, transactions…difference between aligned and misaligned reads was the same and, WT -vs- WB cache of course showed no difference… Summary For the two workloads that are …

Filed Under: MySQL
« Older Posts
Newer Posts »