v8. VoltDB Decapitates Six SQL Urban Myths and Delivers Internet Scale OLTP in the Process. What do you get when you take a SQL database and start a new implementation from scratch, taking advantage of the latest research and modern hardware?
Mike Stonebraker, the sword wielding Johnny Appleseed of the database world, hopes you get something like his new database, VoltDB: a pure SQL, pure ACID, pure OLTP, shared nothing, sharded, scalable, lockless, open source, in-memory DBMS, purpose-built for running hundreds of thousands of transactions a second. VoltDB claims to be 100 times faster than MySQL, up to 13 times faster than Cassandra, and 45 times faster than Oracle, with near-linear scaling. Will VoltDB kill off the new NoSQL upstarts? Will VoltDB cause a mass extinction of ancient databases? Probably no and no to both questions, but it's a product with a definite point-of-view and is worth a look as the transaction component in your system. I first heard the details about VoltDB at Gluecon, where Mr. VoltDB's Architecture John Hugg, from VoltDB Engineering, says: And: Problem: Index Combine Performance in SQL (Oracle, MySQL, PostgreSQL)
It is one of the most common question about indexing: is it better to create one index for each column or a single index for all columns of a where clause?
The answer is very simple in most cases: one index with multiple columns is better—that is, a concatenated or compound index. “Concatenated Indexes” explains them in detail. Nevertheless there are queries where a single index cannot do a perfect job, no matter how you define the index; e.g., queries with two or more independent range conditions as in the following example: SELECT first_name, last_name, date_of_birth FROM employees WHERE UPPER(last_name) < ? AND date_of_birth < ? It is impossible to define a B-tree index that would support this query without filter predicates.
If you define the index as UPPER(LAST_NAME), DATE_OF_BIRTH (in that order), the list begins with A and ends with Z. No matter how you twist and turn the index definition, the entries are always arranged along a chain. Note One index scan is faster than two. Use The Index, Luke! SQL performance problems are as old as SQL itself—some might even say that SQL is inherently slow.
Although this might have been true in the early days of SQL, it is definitely not true anymore. Nevertheless SQL performance problems are still commonplace. How does this happen? The SQL language is perhaps the most successful fourth-generation programming language (4GL). Its main benefit is the capability to separate “what” and “how”. SELECT date_of_birth FROM employees WHERE last_name = 'WINAND' The SQL query reads like an English sentence that explains the requested data. The separation of concerns—what is needed versus how to get it—works remarkably well in SQL, but it is still not perfect.
It turns out that the only thing developers need to learn is how to index. This book covers everything developers need to know about indexes—and nothing more. In June I offer several online trainings on sql performance.Check it out! The B-tree index works almost identically in many databases.