CODEWITHSUNDEEP
mysqlindexingmariadb
John
John

Reputation: 13756

Comparison of MariaDB Indexes

I'm using HeidiSQL and I'm starting to research indexes as there is one table in particular that runs queries noticeably longer as it's populated with 1,249,848 rows. Almost every table (with the reasonable exceptions) have a primary key; that was pretty straight forward. I came across Socratica's SQL Index video which is a fantastic watch for those with limited SQL experience in general though unfortunately she failed to clarify which type of index one should use.

HeidiSQL allows the creation of primary, key, unique, fulltext and spatial indexes, at least with MariaDB and I've been exclusively using MariaDB for the past few years. The table I plan to add a couple of indexes are for the date and engine columns for my spider log table. I use DATETIME and VARCHAR() for those respective columns so I have an idea of which types of indexes I want to use though I would very much appreciate a comparison of the indexes with suggestions. The manual covers what the indexes do though don't quite seem to cover the contrast of which sets of data they are most appropriate for in comparison to each other index.

Upvotes: 1

Views: 969

Answers (1)

Kaii
Kaii

Reputation: 20550

There can only be one primary key on each table, and you should always have one defined. The table data (on disk) will be stored in a way that searching for the primary key is very fast. (called Clustered Index)

In most applications, the primary key is an auto_increment surrogate key.
But you might also choose a natural primary key, that is a unique identifier already occuring in your data like serial numbers, MAC addresses, social insurance numbers, ...

Searching in the primary key is the fastest way to retrieve data, but there can only be one.

When talking about indexing to speedup searches, people usually mean simple KEYs, also called "secondary keys".

For many use cases, it is useful to create a "covering index" that contains more than one attribute.
i.e. KEY(lastname, surname) speeds up a query like
WHERE lastname="Anderson" AND firstname="Thomas"
as well as
WHERE lastname="Anderson"
but not
WHERE firstname="Thomas"

It is very important to know what queries your application is executing the most and design your indexes for these:

Not every query can effectively use an index, not all indexes are useful, and under high write loads, it's better to have a few good ones than many poor ones.

Indexes are write expensive: every update on a row means also an update to each individual index!

The other types are special:

unique keys ensure that only a single row can hold the same value for an attribute. This is usually used to enforce business rules like "e-mail addresses of accounts must be unique"/"only one account per e-mail".

fulltext can only be applied to TEXT columns and is actually a mini search engine embedded in the database. You can use AND and OR conditions, wildcards with the special MATCH operator for text search.

and spatial is for coordinate systems / geometry and cannot be used for anything else.

Upvotes: 1

Related Questions