Description:
Hi,
i'm not completelly sure about how it works on mysql, but if i'm not wrong, on mysql the index of primary key is the one ho determines how the table data will be "organized" on the phisical files.
As the few that i can understand this issue is something that is implemented at the engine level.
In some contexts of my job functions i've to deal with Ms Sql Server that allows me to create a Clustered index per table. That means that table is physically ordered by this index.
Deppending of the DB architecture it could mean a very great performance increasement on some queries, just because that at the end of execution for example "in 75% of my results are ordered by the name of the department" and i just dont need do make a "order by operation" that causes a big overhead on execution.
I know that some people could not agree with that architecture, but for example: why should i have a table that contains a range of data that is displayed on 90% of cases with a specific column order Example (`city_name` ASC, `consultor_name` ASC) phsysically ordered by a filed `id_table`.
this Clustered index defines at the same time how the data is persisted on hard drive and at the same time the order for a basic resultset from a query take a look for one more example:
if i create a table to store data from a processed result that gives me total of the visits for my website urls, and imagine that is a large website with lots of possible urls to process dailly. This table have only 4 fields (`log_id`<pk>,`view_date`,`url`,`hits`) i'll have lots and lots and lots of rows on this table, if i get a clustered index ON table(`view_date` DESC,`hits` DESC), i just have to make a "select * from table limit 0, 5" to know what url is the most visited today, and that is the kind of information that i need to get from this table for my application dashboard on 90% of times.
i'm not expert on this "low level implementation" but i think that it could give us a lot of benefits on performanse issues.
if it is possible to implement, i think that can be very usefull on InnoDB and MyIsam engines.
thanks in advance
How to repeat:
just a suggestion
Suggested fix:
just a suggestion
© 2022, Oracle Corporation and/or its affiliates