For both small free database space and large e-commerce websites, reasonable database table structure design is essential. To achieve this, it requires us to have a full understanding of commonly used data types in database system. Below we share some knowledge about data types in MySQL.1. Numeric typesThe numeric types can be classified as : integer, float and decimal type.The so-called "decimal" refers DECIMAL and NUMERIC, they are of the same type. Strictly speaking it is not a numeric type, because they are actually stored as strings; Every single digit of its value(including the decimal p...

I have been playing around with Hybrid Word Aligned Bitmaps for a few weeks now, and they turn out to be a rather remarkable data structure.  I believe that they are utilized extensively in modern column oriented databases such as Vertica and MonetDB.Essentially HWABs are a data structure that allows you to represent a sparse bitmap (series of 0's and 1's) really efficiently in memory.  The key trick here is the use of run length encoding to compress the bitmap into fewer bits while still allowing for lightening fast operations.  They key operation from my perspective ...

Starting from a problemFive years ago when I was working at Tencent, I found that MySQL request speed was very slow in the pagination scenario. With only 100,000 data, a select query on a single machine took about 2-3 seconds. I asked my mentor why, and he asked in return, "In an indexing scenario, what is the time complexity to get the nth largest number in MySQL?"The pursuit of the answerConfirming the scenarioAssuming there is an index on the "status" column, a query like "select * from table where status = xx limit 10 offset 10000" will be very slow. Even with a small amount of data, there...

An important aspect to keep in mind with databases is the cost of cache reheating after a server restart. Consider the following diagram which shows several cache servers (e.g., memcached) in front of a database server.This sort of setup is common and can work quite well when appropriate; it removes read load from the database and allows more RAM to be utilized for scaling (when the database doesn’t scale horizontally). But what happens if all the cache servers restart at the same time, say, on a power glitch in a data center?We then have a cache reheating scenario. After the bounce the...

SELECT MEMBER.Name, MEMBER.Address, ORGANIZER.phoneNo, TRAVEL.Tour_Name, TRAVEL.Start_Date, TRAVEL.End_DateFROM TRAVEL RIGHT JOIN ((MEMBER LEFT JOIN ORGANIZER ON MEMBER.Member_ID=ORGANIZER.memberID) LEFT JOIN PARTICIPATION ON MEMBER.Member_ID=PARTICIPATION.MemberID) ON TRAVEL.TravelID=PARTICIPATION.TravelID;...

Cassandra is an open source distributed database, it combines dynamic key/value and column oriented feature of Bigtable.Features of Cassandra are:Flexible schema, no need to design schema first, it's very convenient to add or delete stringsSupport range search on keysHigh usability, extensible. The single node error will not affect the cluster.We can think Cassandra's data model as a 4 or 5 dimensional Hash.COLUMNColumns is the smallest data unit in Cassandra, it is a 3 dimensional data type including name,value and timestamp. { // This is a column name: "逖靖寒的�...

The MySQL family has been innovating rapidly. New features need names and sometimes those names are confusing. Describing something as multi-master or a NoSQL solution has confused me.Multi-master requires one of conflict prevention, conflict resolution or faith. MySQL Cluster provides both conflict prevention and resolution as described in these great posts. Regular MySQL has minimal support for conflict prevention (auto-increment-offset can prevent insert conflicts) and thus requires faith that the application does the right thing. Regular MySQL gets conflict pr...

In some situations, MySQL will just use an index to fulfill the requirement of an ORDER BY or GROUP BY statement without extra sorting.Although ORDER BY will not have the exact match with index, index can still be used as long as the portion that is not included in the index is included in the where clause.The following queries will all use index to process the ORDER BY or GROUP BY part:SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;SELECT * FROM t1 ORDER BY key_part1 D...