As figure 1211 shows an index is a separate data

This preview shows page 458 - 460 out of 502 pages.

as Figure 12.11 shows. An index is a separate data structure maintained by the database that uses hashing techniques over the column values to allow constant- time access to any row when that column is used as the constraint. You can have more than one index on a given table and even have indices based on the values of multiple columns. Besides obvious attributes named explicitly in where or find_by_ x queries, foreign keys (the subject of the association) should usually be indexed. For example, in the example in Figure 12.10 , the moviegoer_id field in the movies table would need an index in order to speed up the query implied by movie.moviegoers . Of course, indices aren’t free: each index takes up space proportional to the number of table rows, and since every index on a table must be updated when table rows are added or modified, updates to heavily-indexed tables may be slowed down. However, because of the read-mostly behavior of typical SaaS apps and their relatively simple queries compared to other database-backed systems such as Online Transaction Processing (OLTP), your app will likely run into many other bottlenecks before indices begin to limit its performance. Figure 12.12 shows an example of the dramatic performance improvement provided by indices. # of reviews: 2000 20,000 200,000 Read 100, no indices 0.94 1.33 5.28
Read 100, FK indices 0.57 0.63 0.65 Performance 166% 212% 808% Create 1000, no indices 9.69 Create 1000, all indices 11.30 Performance –17% Figure 12.12: For a PostgreSQL shared database on Heroku containing 1K movies, 1K moviegoers, and 2K to 20K reviews, this table shows the benefits and penalties of indexing. The first part compares the time in seconds to read 100 reviews with no indices vs. with foreign key (FK) indices on movie_id and moviegoer_id in the reviews table. The second part compares the time to create 1,000 reviews in the absence of indices and in the presence of indices over every possible pair of reviews columns, showing that even in this pathological case, the penalty for using indices is slight. Summary of avoiding abusive queries: The n+1 queries problem, in which traversing a 1-to- n association results in n + 1 short queries rather than a single large query, can be avoided by judicious use of eager loading. Full-table scans in queries can be avoided by judicious use of database indices, but each index takes up space and slows down update performance. A good starting point is to create indices for all foreign key columns and all columns referenced in the where clause of frequent queries. E LABORATION: SQL EXPLAIN Many SQL databases, including MySQL and PostgreSQL (but not SQLite), support an EXPLAIN command that describes the query plan : which tables will be accessed to perform a query and which of those tables have indices that will speed up the query. Unfortunately, the output format of EXPLAIN is database- specific. Starting with Rails 3.2, EXPLAIN is automatically run on queries that take longer than a developer-specified threshold in development mode, and the query plan is written to development.log . The query_reviewer

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture