I have a table 'activities'. it consists of around 1 million records. its columns consists off,
id(PK), u_id(FK), cl_id(FK), activity_log
By Default id(PK) is indexed and i have created my own index for u_id and cl_id by using,
ALTER TABLE activities ADD INDEX(u_id,cl_id);
Now i would like create an Multi-Level indexing for cl_id(FK) or id(PK) or for both the columns if its possible. How to create an multi-level index using query?
How to find out a column that is multi-level indexed in a table? i have tried this query but it shows only the column that are indexed,
SHOW indexes FROM activities;
Does Multi-level index and non-clustered index are they both same ?
I have no idea what a "multi-level" index is. But there are "composite" indexes such as
INDEX(last_name, first_name)
which is very useful for
WHERE last_name = 'James'
AND first_name = 'Rick'
or even
WHERE last_name = 'James'
AND first_name LIKE 'R%'
In MySQL (InnoDB in particular), the PRIMARY KEY is always a unique index and it is "clustered" with the data. That is, looking up a row by the PK is very efficient. The structure is always BTree.
"Secondary keys" are also BTrees, but the leaf node contains the PK. So, a second lookup is needed to complete the query. (This distinction is rarely worth nothing.)
The PK and/or secondary keys can be "composite".
Declaring a FOREIGN KEY adds a secondary index if there is not already some suitable (PRIMARY or secondary) index that starts with the column in the FK.
The following is redundant, and should be avoided:
PRIMARY KEY(id),
INDEX(id) -- DROP this
Indexed Sequential
Ouch! I have not heard of that antiquated indexing method in nearly two decades.
The Question gives a link to such as a definition of "multi-level" indexing. Run from it! Change schools. Or at least understand that IS is no longer considered viable.
MySQL uses "BTrees" for most indexing. It was invented decades ago, and essentially wiped out Indexed Sequential. And, to a large extent, has wiped out Hashing as an indexing technique on disk.
MySQL's BTrees can handle multiple columns, as in my example, above.