Hello good evening.
I have a doubt, regarding the indexes, it is for both managers, SQL and MySQL.
I have a Persons Table , in which two types are saved, client or supplier , and the records are stored according to the order in which they are entered. When creating the table the order is by the Primary Key . But I want to avoid performing "order by supplier_type, number, name" but it is already defined in this way.
How could I structure the table so that it is already ordered and the search is easier and faster?
What I want to achieve is something like this with indexes, whether in ascending or descending order, without the need to use order by, with unique if it can be done, but with non-clustered indexes, which supposedly work to make the query more efficient, they don't. I have accomplished.
order by supplier_type, number, name
order by supplier_type, number desc, name
Table with the index of the Primary Key
Table with the created index, remains unchanged
Table with Unique constraint, sort by type of person, number and name, which is the order I want my table structure to have, but I don't want to use Unique in this table
I have read that database indexes work like the index of a book and return queries more efficiently, but honestly I don't see the reason how they really work.
I have always optimized my queries without indexes and have worked with up to 100,000 records, with more than 40 columns, although the best efficiency I achieved was 2 seconds. In some cases, it has helped me to have structured the table well, to have correctly defined the data types, the lengths, to correctly relate the joins, and other times the Unique restrictions, which solved the problem because it was the order that was needed and they coincided with the requirement. to restrict registration based on those fields.
This is a double question.
I will answer the first, and the second vaguely. You have a lot of research to do.
It is not correct that you say with unique if it can be done . I don't see in MySQL's UNIQUE INDEX documentation that it says that it automatically sorts the results. The query may not automatically return sorted based on the indexes.
Why?
The operations of the relational database engines (MySQL, SQL Server, etc...), are based, mostly, on the Set Theory where the order is not important . Therefore, RDBMS in no way guarantee the ordering of results. The indices have NOTHING to do with the order of the results. Order is important to us humans. For math, no.
I don't understand indexes on a table. How do they work?
Depending on the engine you use, the implementation of the indexes may change. For MySQL, indexes are mostly used to improve query speed because each record in the index is a pointer that points directly to the row in the table that has the data you are looking for.
The indexes are, literally, physical structures that go next to the table that has the data, therefore they take up space.
Imagine the following scenario. You have a non-indexed table with all Amazon shipping orders. Imagine that the table has 5 billion records. Imagine that you are looking for a specific order number (3,000,000,000). The RDBMS must do what is known as a Full Table Scan to know where exactly the record you are looking for is. It will search through the records ONE BY ONE to find the one you are looking for.
Now imagine you have an index on the order number.
So there is literally a structure where the RDBMS can perform a binary search , i.e. if you search for the order 3,000,000,000, the engine searches for:
First half of the index, compares the value obtained (2,500,000,000) and realizes that the order you are looking for is after. So, discard all the ones that are before.
Then, he finds half of the remaining orders, this number is 3,750,000,000 and he realizes that the buy order is before, and discards all the following ones.
It then continues this search until it reaches the correct order number. Instead of searching through 5 billion records, it just had to perform the binary search on the index, which is orders of magnitude faster. Once it finds the value it looks for in the index, it has a pointer that points directly to the row containing the information in the purchase order table.
Remember that for an index to be useful, it must go in the WHERE clause of the query. There is no use having an index on the order number, if you are looking for orders by date. In this case, you better put an index on the date columns.
I hope this can clear up some of the confusion about indexes that many developers have in our lives. This explanation is legacy and it helped me understand indexes better.