#DBHangOps 07/24/14 -- More Indexing!
All done for this week! Check out the recording below:
Join in #DBHangOps this Thursday, July, 24, 2014 at 11:00am pacific (18:00 GMT), where we pick up on our last conversation about indexing:
- More discussion on geo spatial indexes
- Fulltext indexing and ranking
- MariaDB indexing features (From Gerry!)
- Anything else indexing!
Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!
See all of you on Thursday!
Change Buffer and B+ Trees
Clustered vs. Non-clustered indexes
- What are they?
* You have an index that simply points to records inside of a table.
* You can have multiple indexes that point to the same pieces of data
- Clustered index
- The leaf nodes of a clustered index include the data right with the index (no pointers)
- InnoDB stores table data in a clustered index (primary key)
- Secondary indexes in MySQL are non-clustered indexes
- Keep in mind that unless your secondary index covers/satisfies all the fields of a query, you're incurring a cost to do a Primary Key lookup as well
- InnoDB provides a feature called the Adaptive Hash
- This is a quick lookup structure to avoid traversing the secondary index
- There can be some mutex contention on this structure in busy enough workloads
- Warning! If you don't define a Primary Key, InnoDB will automatically make one for you
- There's a shared structure that is used to watch for PK increments for tables that don't have a defined primary key. This could be a point of contention!
- AUTO_INCREMENT has been improved over the years to deal very well with contention issues!