Here are some typical query problems Shiba can detect. We’ll assume the following schema using Rails / Active Record:
create_table :users do |t| t.string :name t.string :email # add an organization_id column with an index t.references :organization, index: true t.timestamps end
The most simple case to detect are queries that don’t utilize indexes. While it isn’t a problem to scan small tables, often tables will grow large enough where this can become a serious issue.
user = User.where(email: 'firstname.lastname@example.org').limit(1)
Without an index, the database will read every row in the table until it finds one with an email address that matches. By adding an index, the database can perform a quick lookup for the record.
Another common case is queries that use an index, and work fine in the average case, but the distribution is non normal. These issues can be hard to track down and often impact large customers.
users = User.where(organization_id: 1) users.size # => 75 users = User.where(organization_id: 42) users.size # => 52,000
These queries can easily slip by in a test environment: your typical developer will keep a small dataset around to test with, and may be blind-sided by production issues in code.