Join Sheeri Cabral for an in-depth discussion in this video Indexing CONNECT tables, part of Understanding MariaDB for MySQL Users.
- View Offline
…You can put indexes on your MariaDB tables that connect to…remote files and databases.…Not all remote table types can be indexed.…For example, the XML table type can not be indexed.…Let's take a look at our birthdays CSV table.…We're going to do a SHOW CREATE TABLE birthdays.…Let's explain a select to show that we are currently not using an index.…So let's do an EXPLAIN SELECT * FROM birthdays where FIRST equals Grace.…
And indeed, you can see that the type field is all,…which means we are doing a full table scan.…Now let's add an index.…We will use standard MySQL syntax to add and…drop indexes, even in the CONNECT storage engine, connecting to different files.…So let's do ALTER TABLE birthdays ADD INDEX on first.…Let's repeat our EXPLAIN, and see if the index is used now.…So we hit the up arrow.…Now the type is not ALL, which means it is using an index.…
Specifically, the key field lets us know it is using the index called first.…There are some limitations to indexing CONNECT tables that are reading files.…
- Why use dynamic columns?
- Creating, updating, and querying dynamic columns
- Installing the CONNECT storage engine
- Connecting to remote data
- Connecting to CSV, INI, and XML files
- Using PIVOT tables
- Accessing database metadata
- Working with the Cassandra storage engine