I have a question, does the database cluster index store the whole row of data? So if you have a lot of columns, the index is still looking at the whole row of data. So does having more columns in a query cause mysql queries to slow down?
select * from tableName
2
Answers
Yes, an index will only be used when there is some criteria in your query.
E.g.
WHERE type = 'admin'is there in your query,And there is an index on
typecolumn in your table, and it has limited distinct values, then it will use index.Refer: https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
If your query is
SELECT * FROM tableName, then it has to go through the whole table anyway. There is no point in using any index, even if those are there.Only when it has to search for some subset of records, using where criteria, it will decide which index might help in going through the least amount of records to find what you are looking for.
Assuming you are using InnoDB as the storage engine. This is the default in MySQL.
Yes, all the columns are stored together in the clustered index. The whole row is stored in the same InnoDB page. Except for BLOB/TEXT/JSON, which may overflow to additional pages.
Since InnoDB reads data in units of pages, once you read one column from the row, the rest of the row is also included in that read. The extra cost of reading all the columns is negligible compared to initially reading the page.
By analogy, if you are reading a book and you find a page that has the one sentence you are looking for, it’s very little effort to read the rest of that page while you’re there.
Since BLOB/TEXT/JSON columns may be large enough to overflow to additional pages, doing
SELECT *when you don’t need those columns does cost more, because InnoDB needs to read the additional pages. This can be a good reason to avoid usingSELECT *, and be more specific to name only the columns you need per query.