Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying MySQL Admin Cookbook LITE: Replication and Indexing
  • Table Of Contents Toc
MySQL Admin Cookbook LITE: Replication and Indexing

MySQL Admin Cookbook LITE: Replication and Indexing

Buy this Book
close
close
MySQL Admin Cookbook LITE: Replication and Indexing

MySQL Admin Cookbook LITE: Replication and Indexing

Buy this Book

Overview of this book

Table of Contents (3 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Lock Free Chapter
1
1. Replication
Icon
1. Replication
Icon
Introduction
Icon
Setting up automatically updated slaves of a server based on a SQL dump
Icon
Setting up automatically updated slaves of a selection of tables based on a SQL dump
Icon
Setting up automatically updated slaves using data file copy
Icon
Sharing read load across multiple machines
Icon
Using replication to provide full-text indexing for InnoDB tables
Icon
Estimating network and slave I/O load
Icon
Limiting network and slave I/O load in heavy write scenarios using the blackhole storage engine
Icon
Setting up slaves via network streaming
Icon
Skipping problematic queries
Icon
Checking if servers are in sync
Icon
Avoiding duplicate server IDs
Icon
Setting up slaves to report custom information about themselves to the master
2
2. Indexing
chevron up
Icon
2. Indexing
Icon
Introduction
Icon
Adding indexes to tables
Icon
Adding a fulltext index
Icon
Creating a normalized text search column
Icon
Removing indexes from tables
Icon
Estimating InnoDB index space requirements
Icon
Using prefix primary keys
Icon
Choosing InnoDB primary key columns
Icon
Speeding up searches for (sub)domains
Icon
Finding duplicate indexes

Using prefix primary keys

In this example, we will add indexes to two tables that are almost identical. The only difference will be the definition of their primary keys. You will see the difference in space consumption for secondary indexes between a regular full column primary key and a prefix primary key. The sample table structure and data are designed to demonstrate the effect very evidently. In real-world scenarios the effect will most certainly be less severe.

Getting ready...

Connect to the database server with your administrative account.

How to do it...

  1. Download the sample script for this chapter from the book's website and save it to your local disk. In the example below, we will assume it is stored in /tmp/idxsizeestimate_sample.sql.
  2. Create a new database and make it the default database:
    CREATE DATABASE pktests;
USE pktests;
  3. Import the sample data from the downloaded file. When done, you will be presented with some statistics about the two tables loaded. Note that both tables...
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
MySQL Admin Cookbook LITE: Replication and Indexing
End of Section 2
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon