Apr
15
Database sharding unraveled - part I
Filed Under MySQL, Programming, Scalability tools | 9 Comments
This is the first post from a hopefully long series to come, about Database Sharding. 
The best way I can think of to define the concept is to associate it with ice fragments (build them up and you can sculpt anything, but failing to provide the right temperature collapses it all).
The idea is to split tables in a database in what are called shards, or fragments, or pieces. As your application increases in size, you need a way to scale cheap, efficiently and limitless. Furthermore, minor changes to the already existing code are required (buying more hardware is usually cheaper than re-programming).
There are several ways of dealing with database sharding, each with its pros and cons:
- application layer
- proxy
- database layer
Of course, many other methods exist, but they are only implementations at some extent of the above.
Apr
13
Conditional statements inside MySQL queries
Filed Under MySQL, Programming | 5 Comments
Often, when updating different values inside a MySQL table, extra selects, for example, might be needed. But that’s not always the case, because conditional statements can be used inside ordinary queries.
Let’s take a real world example: the table which holds informations about the timing in a test. So, the user is presented a javascript counter and upon submitting, the elapsed time is checked.
UPDATE $table_name SET end_time= IF(UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP(start_time) < $number_of_seconds, CURRENT_TIMESTAMP, '') WHERE timing_id='$timing_id'
The IF statement takes 3 parameters: the expression to be evaluated, the value if the evaluation returns true, the value if the evaluations returns false (in the above example, the value is blank - ”, which means the default field value will be inserted).