MYSQL Best Practices

1. MyIsam vs InnoDB Feature MyISAM InnoDB Memory ACID Transaction No Yes No Configurable ACID Properties No Yes No Crash Safe No Yes No (RAM) Foreign Key Support No Yes No Multi Version Concurrency Control (MVCC) No Yes No Geospatial datatype Yes Yes No Geospatial indexing Yes No No Full-text Search Index Yes No No Data …

Continue reading

Innodb locking and Foreign Keys

Today I was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little investigation on that matter. Interesting enough it looks like most people do not think about foreign keys overhead in terms of locking. The overhead about checking referenced table …

Continue reading

MySQL datatype for price

The basic difference between Decimal/Numeric and Float : Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly. Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale. MySQL datatype for …

Continue reading

MySQL Optimizing Schema and Data Types

1. Choosing Optimal Data Types Smaller is usually better Smaller data types are usually faster, because they use less space on the disk, in memory, and in the CPU cache. They also generally require fewer CPU cycles to process. Simple is good Fewer CPU cycles are typically required to process operations on simpler data types. …

Continue reading

[Note] MySQL Architecture and Concepts

MySQL’s Logical Architecture The topmost layer contains the services that aren’t unique to MySQL. They’re services most network-based client/server tools or servers need: connection handling, authentication, security, and so forth. The second layer is where things get interesting. Much of MySQL’s brains are here, including  the  code  for  query parsing,  analysis,  optimization,  caching,  and  all  …

Continue reading

[Ebook] Oreilly.High.Performance.MySQL

Dạo này gặp khá nhiều vấn đề với MySQL nên quyết định sẽ nghiên cứu thêm về nó và sau một hồi miệt mài với Mr.Google thì thấy cuốn ebook này mình cũng khá hợp ý. Mục đích cơ bản tại thời điểm hiện tại khi đọc cuốn sách này là: Kiến trúc, cách MySQL …

Continue reading