Architectural Scalability | 173We can see from this example that there are already sufficient indexes on the table,
and they are being used properly. This is probably as fast as the query will get given
the table size, so there is not much to be gained from more tweaks.
In MySQL, the syntax is the same (without theANALYZEkeyword), but the output is
substantially different. MySQL’s syntax is more simplistic, but it is arguably easier to
understand.
mysql> EXPLAIN SELECT * FROM default_en_listingsdb ldb
INNER JOIN default_en_listingsdbelements ldbe
ON ldb.listingsdb_id = ldbe.listingsdb_id
WHERE ldb.listingsdb_id = 141054;
+----+-------------+-------+-------+----------------+----------------+-------+------+
| id | select_type | table | type | possible_keys | key | ref | rows |
+----+-------------+-------+-------+----------------+----------------+-------+------+
| 1 | SIMPLE | ldb | const | PRIMARY | PRIMARY | const | 1 |
| 1 | SIMPLE | ldbe | ref | idx_listing_id | idx_listing_id | const | 40 |
+----+-------------+-------+-------+----------------+----------------+-------+------+
2 rows in set (0.35 sec)Rather than defining the steps the database engine takes to fulfill the query, this syn-
tax shows the tables that contribute to the query. As this is a simple inner join, the
two tables that comprise theFROMclause are listed, and theselect_typeof both is
SIMPLE (which indicates that they are not components of a union or subquery).
TheWHEREclause restricts the first table to one row, referenced by the primary key.
The type of that lookup isconst, which is the fastest type. Thepossible_keyscol-
umn shows the possible indexes that could satisfy the query (PRIMARY), and thekey
column shows the one that the query planner chose.
The second table, indexed by foreign key, uses areflookup on the index, which is
fast enough in this case as the number of rows returned (40) is small. If we had not
defined theidx_listing_idindex, the type would beALLrather thanref, indicating
that the entire table must be scanned.
A basic understanding of your database’s query planner is vital to writing good que-
ries and tracking down bad ones. Even for PostgreSQL and MySQL, freely available
open source databases, the documentation is very readable and of excellent quality.
Architectural Scalability
One of the hardest parts of building and deploying a web application is growing it.
Luckily, Rails was designed with scalability in mind. The Rails scalability mantra is
shared-nothing—the idea that each application server should stand on its own, not
having to coordinate with other application servers to handle a request. The only
thing that needs to be shared when scaling upward is the database.*
- Some would consider a shared database to be shared-something, but I digress.