0

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 ?

James Z
  • 12,209
  • 10
  • 24
  • 44
Crysis
  • 418
  • 2
  • 7
  • 28
  • multi-level index? What on earth should that be? – fancyPants Jan 12 '18 at 14:06
  • @fancyPants http://theteacher.info/index.php/architecture-data-comms-and-applications-unit-5/4-organisation-and-structure-of-data/all-topics/3940-multi-level-indexes – Crysis Jan 12 '18 at 14:32
  • I just overflew the article, but that has nothing to do with MySQL indexing. There are no multilevel indexes in MySQL (or any other database I know of). If you're interested in how indexes work in MySQL I can recommend this site: http://use-the-index-luke.com/ – fancyPants Jan 12 '18 at 14:41

1 Answers1

1

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222