Composite Indexes in MySQL
My last MySQL article talked about the basics of indexes in MySQL, now let’s do more with what we know. Introducing Composite Index, the kind of index that is comprised of more than 1 column. Read on.
So let’s start with a simple table like below.
The only index is the Primary Key, no other index is created yet. Here’s our initial data.
For our next task, we want to query MySQL based on the user’s first and last name. Here’s the query and the result.
The above done, let’s add the EXPLAIN statement like we did before.
As you can see, MySQL scanned through the whole table rows to get the result. Let’s try to add an index for both the first_name and last_name column. Afterwards, let’s rerun the EXPLAIN query one more time.
We can see from the EXPLAIN result that MySQL got the result directly by using our newly created composite index.
Now that we have our footing firmer, we need to know what the columns mean whenever we do an EXPLAIN. For the sake of brevity, I’m pasting the explanation from here below.
On my next article, let’s talk more about utopian ideals when planning for queries. Until then, Cheers!
