COMMON RULES OF INDEXING, QUERYING, DATA MODELING • Let’s visualize them • How do they work • What are they • When/Why do they break down • Tools you can use • How to move forward Overview ! !
an index? • An index is a copy of selected columns of data from a table that can be searched quickly and efficiently • Index is sorted • Has as many rows as the table (1 row in the index for 1 row in the table itself) • Indexes and tables are really the same thing - but you can get to the data you need in an index faster and then lookup whatever else you need in the main table knowing exactly where to look
• Index contents are used left to right • Example • ‘SELECT * FROM repositories where name = ‘rails’ ‘ would NOT use the index Index over owner_id_and_name
the rule. • We’re not really breaking it, we’re adding a caveat of “unless there is already a covering index.” Rule #1: Any columns involved in queries should be covered by an index
matter? • So we have a redundant index. So what? • Takes up precious space • Adding an index slows down UPDATEs, INSERTs and DELETEs, because those operations need to be performed on the table and the index
the rule. • When should you use an index prefix instead of a regular index? • long datatype (text, blob, varbinary) • querying the data works well with a prefix (i.e. usernames starting with ‘a’) Rule #1: Any columns involved in queries should be covered by an index
to use an index prefix • How long should we make the prefix? • long enough to differentiate values within this field • base it off of real data if possible “I chose 255 bytes as the prefix because the longest path in github/github is 199 bytes”
to use an index prefix • What do we gain from using an index prefix? • less space required for the index • ability to index big data types • comparable performance improvement as a full index if length calculated thoughtfully or query tuned for index prefix
with? 33 ! • Indexes with columns in our query: • `index_users_on_spammy` (`spammy`) • `index_users_on_suspended_at` (`suspended_at`) • the `OR` operator is limited because MySQL can only use one index per table during a query, so it chooses to use neither
34 ! • The table being queried isn’t small, so a full table scan is not performant • The use of the ‘OR’ is preventing any index from being used Rule #2: Use an OR to return records satisfying one or more of several conditions
to return records satisfying one of more of several conditions* 37 ! *except when the OR is preventing an index from being used and you have to do a full table scan
42 ! • MySQL is choosing to use an index that isn’t performant, or no index at all when one is available Rule #3: If there’s an index over all the fields in your query, you’re all set
there’s an index, you’re all set • Other options • USE INDEX - more of a hint • IGNORE INDEX - explicitly state which index not to use, but let MySQL decide from the remaining indexes
“Give me all these Pull Requests, excluding those opened by spammy user” “Give me this group of repositories, except those owned by a spammy user” “Give me all these users’ gists, unless the user is spammy”
50 ! • Additional reads (JOINs) are causing noticeable performance degradation •High ratio of reads to writes Rule #4: Avoid redundant data across tables
DATA QUALITY • Lots of migrations to add new column to tables • Data transitions to backfill data • The `user_hidden` column is updated in the tables via a background job after a user is marked as spammy • Nightly job to resolve any mismatches b/w the tables Gotchas. #
across tables* 55 ! *except when you are JOINing on another table for almost every request, and it’s getting costly, and you have a high volume of reads to writes for this data
#1: Any columns involved in queries should be covered by an index • *but avoid redundant and unused indexes • *sometimes an index prefix is needed and sufficient • Rule #2: Use an OR to return records satisfying one or more of several conditions • *but a UNION might be necessary to exploit the indexes • Rule #3: If there’s an index over all the fields in your query, you’re all set • *if MySQL is making the wrong choice, help it make the right one • Rule #4: Avoid redundant data across tables • *except when reads are slow, read/write ratio is high, you can denormalize
but don’t over index • Tune your query to exploit the indexes that give you the best results • There are tools to help you • explain queries • examine existing columns and indexes • You can “do everything right” and still have performance problems
bryanaknight
oracle4engineer
vtryo
knih
laysakura
shoota
seike460
mu7889yoon
daitak
rince
junki
yoshimi0227
hamukazu
addyosmani
codingconduct
jeffersonlam
brad_frost
bluesmoon
techseoconnect
tanoku
portentint
madoxten
reverentgeek
elsirapls
