What is the difference between Primary key and index in mysql?

Question

I was wondering if there’s any difference between Primary key and index. Pq in mysql has several types of indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT).

Answer 1

An index is simply a helper when searching (for example, a SELECT) on top of a column. For example, in a table with people names, adding an index to the NAME helps speed up searches by name, and also selections that get lines in alphabetical order of name.

Searching a field without index takes a linear time proportional to the number of lines. Searching in an index-free field takes a time proportional to the logarithm of the number of lines. When there are 100 million lines, it makes quite a difference, because log(100 million) = 8.

A UNIQUE index indicates that there can be only one copy of the column for each row. This is appropriate for columns that are known to be unique (CPF type of people) but would not be appropriate for names, because homonyms are very common.

PRIMARY KEY, or primary key, is a different concept of index, although usually Primary key is indexed to speed up the search. A primary key is a column that can only have one copy for each row And this column serves to identify this line. CPF could be a primary key for a table of individuals, but the most recommended is to use an abstract primary key - an increasing sequential code or a hash, to facilitate later changes (If your individual table needs to include foreigners in the future, the CPF is no longer a good primary key and can no longer be indexed UNIQUE).

FULLTEXT I don’t know but I assume it is an index to facilitate searches involving parts of a text column (like, search by name fragment instead of full name)