The explain command

The explain SQL command provides information for a specific request. Most of the time, we get a query from the slow query logs to analyze the request. The explain command won't return the classical output of the query but will provide some information concerning the related SQL query.

The explain command can only be applied on a SELECT query. UPDATE and DELETE are supported in Version 10.0.5!

Let's take a query that you can have in your slow query logs. Here is an example with a working version of MediaWiki:

MariaDB [mediawiki]> explain select page_id, page_title, page_namespace, page_is_redirect, old_id, old_text from wiki_page, wiki_revision, wiki_text where rev_id=page_latest and old_id=rev_text_id\g;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: wiki_page
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 2005
 Extra:
*************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: wiki_revision
 type: eq_ref
possible_keys: rev_id
 key: rev_id
 key_len: 4
 ref: mediawiki.wiki_page.page_latest
 rows: 1
 Extra:
...
3 rows in set (0.00 sec)

The explain feature lists two rows here. If you examine the first one, ALL means there is a full scan done on the wiki_page table. Then, in the type section, you can see how the table is accessed. Here, there is no index type. That's why 2005 rows were scanned and that's why it's slow.

If you now look at the second row, it's better. There is an index (eq_ref), which means this is the best possible plan to find the row. In addition, the number of scanned rows is 1, so it's perfect!