InnoDB Clustered Index is stored under memory?
firstly i'm newbie about internals databases, sorry if my question is dumb.
I have read in Stackoverflow(post link at final) that InnoDB clustered index is stored in disk and in memory.
And i have read that Clustered Index Leaf node have row data.
My question is: Based on the above affirmations, if my database has a huge table and such table has a clustered index, such clustered index will consume a large space of my memory?
If the answering for my question is Yes, isn't it a bad thing? isn't better just have secondary indexes in memory and access disk based on secondary indexes results?
Index and data pages are stored in the innodb buffer pool. Only the active part of these indexes and are loaded. The least recently used data/index is purged out of the innodb buffer pool (memory).
Create a primary key, which is therefore a clustered index, of the smallest, commonly searched, unique column(s) for the table.
Create the secondary indexes you need to improve the performance of queries.
Have enough innodb buffer pool to fit the active working set of data in memory. Look at
innodb_buffer_pool_read_requestsin the global status to see what is coming off disk and what is still in memory.