UPDATE: SQLFiddle integration has been removed in 2015 due to the low usage and high effort on my side.
Today marks the third anniversary of Use The Index, Luke! And I have to fulfill a promise I gave one year ago: You can now test many of the example from this site online at SQLFiddle.com.
Here is a trivial example how it looks like. Just click on the SQL Fiddle logo on the right top corner of the execution plan.
SELECT first_name, last_name
FROM employees
WHERE employee_id = 123
AND subsidiary_id = 30- DB2
Explain Plan -------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 13 2 | FETCH EMPLOYEES | 1 of 1 (100.00%) | 13 3 | IXSCAN EMPLOYEES_PK | 1 of 10000 ( .01%) | 6 Predicate Information 3 - START (Q1.EMPLOYEE_ID = +00123.) START (Q1.SUBSIDIARY_ID = +00030.) STOP (Q1.EMPLOYEE_ID = +00123.) STOP (Q1.SUBSIDIARY_ID = +00030.)- MySQL
+----+-----------+-------+---------+---------+------+-------+ | id | table | type | key | key_len | rows | Extra | +----+-----------+-------+---------+---------+------+-------+ | 1 | employees | const | PRIMARY | 10 | 1 | | +----+-----------+-------+---------+---------+------+-------+- Oracle
--------------------------------------------------------------- |Id |Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 |SELECT STATEMENT | | 1 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | |*2 | INDEX UNIQUE SCAN | EMPLOYEES_PK | 1 | 1 | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID"=123 AND "SUBSIDIARY_ID"=30)- PostgreSQL
QUERY PLAN ------------------------------------------- Index Scan using employees_pk on employees (cost=0.00..8.27 rows=1 width=14) Index Cond: ((employee_id = 123::numeric) AND (subsidiary_id = 30::numeric))- SQL Server
|--Nested Loops(Inner Join) |--Index Seek(OBJECT:employees_pk, | SEEK:employee_id=@1 AND subsidiary_id=@2 | ORDERED FORWARD) |--RID Lookup(OBJECT:employees, SEEK:Bmk1000=Bmk1000 LOOKUP ORDERED FORWARD)
As I said—a trivial example borrowed from chapter 2.
I have to admit that not all examples are available at SQL Fiddle yet. At the moment I’m finishing the examples of chapter 2. However, if you have read SQL Performance Explained you know that chapter 2 makes up half of the book. In other words, half of the book is already available at SQL Fiddle.
I hope this online experience makes Use The Index, Luke an even more awesome learning resource. A large part of this additional awesomeness is owed to Jake Feasel who built SQL Fiddle. Please note that you can flattr SQL Fiddle and donate via PayPal (on the right top of the page).
If you have not yet read the book, please have a look at the table of contents now and remember that you are just one click away from actually running the examples shown in the book. Learning about SQL performance has never been that easy ;)
