Can you give a clear example of an index on tables?
There is so much written in the docs, it is misleading.
Is the index a primary or foreign key?
Answer 1, authority 100%
In short, the index, this field by which the search is optimized (accelerated).
Since the index takes up space, only those fields that are being sampled need to be indexed.
Let’s say there is a table.
CREATE TABLE MyGuests (
id INT (6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR (30) NOT NULL,
lastname VARCHAR (30) NOT NULL,
email VARCHAR (50),
reg_date TIMESTAMP
)
id is already an index
Let’s say you want to search by name (firstname).
SELECT * FROM MyGuests WHERE firstname = "Vasya"
then it makes sense to add an index on this field.
CREATE INDEX firstname_index ON MyGuests (firstname) USING BTREE;
A “map” will be generated that makes it easy to find entries in the original list.
https://ru.wikipedia.org/wiki/B-%D0%B4%D0%B5%D1%80%D0%B5%D0%B2%D0%BE
For one small table, the benefits won’t be obvious & lt; 1000 records, but only until you try to join several (3-4 are enough) tables on non-indexed fields. Kills the server at once!
Answer 2, authority 56%
In short, indexes are created to improve the performance of data searches. Tables can have a huge number of rows, which are stored in no particular order. Without an index, the search for the required rows goes in order (sequentially), which is time-consuming on large amounts of data.
Index – usually one or more columns of the table and pointers to the corresponding rows of the table, allows you to search for rows that meet the search criteria. Speeding up work using indexes is achieved primarily due to the fact that the index has a structure optimized for search – for example, in a MySQL b-tree. It is better to use the index on those columns of the table on which you will most often impose conditions through where column_name = ...
The index is created according to the rule:
create index index_name
on table_name (column_name)
For example, you have a table called test , which stores data on cities in Russia with streets of the form City, Street, House. It is clear that there will be a lot of rows in the table in this situation. If you often select a specific city, for example:
select *
from test
where city = 'Omsk'
to make this query work faster than usual, you should add an index according to the above rule:
create index city_index
on test (city)
Then the same request
select *
from test
where city = 'Omsk'
is much faster if the city column is indexed.
Answer 3, authority 28%
On the fingers can be explained as follows:
When you create a table, add data to it, the table grows and it looks like just a sequential list, ordered by how data was added to it.
When there is little data, the list is small and all requests to it are executed almost imperceptibly. But when the number of records in the table starts to exceed a million (in different cases in different ways, but as an example, a million), then your search is not going so fast and with the addition of more and more new records – even slower.
This is due to the fact that when you search for a record, all records are scanned until they reach the desired one.
When you finally get bored of this and you want to do something, then indexes come to your aid.
The index is created on a specific field (possibly several) on which, as a rule, a search is performed. When you create an index, MySql (and any other database) traverses all the records in the table and builds a tree (most likely a B-tree or a variety), in which the selected field is the keys, and the contents are links to the records in the table.
And when you make your next select query on the table for which the index was created, MySql (and any other database) knows that it has an index, which will be faster to walk through than to iterate over everything records and your query will be directed to this index and records that satisfy the condition will be found much faster, since the search in the constructed tree will be much faster than a simple enumeration of all records.