suppose i have a mysql table with rows (originalUrl : varchar(500) , shortUrl : varchar(10))
The queries that will be executed on this table will mostly be
1. select * from table where shortUrl = X
2. insert into table (originalUrl, shortUrl)
So there should be an index on shortUrl to speed this up.
I have the following question -
1. What exactly the index table will store ?
My understanding is index table will store items like - (shortUrl, pointerToDisk) // where pointerToDisk will locate exactly the place in disk where the row is stored.
2. Where is index table stored ?
Is it always stored in Disk or memory ?
3. What is the size of index table exceeds that of RAM ?
In this case the full index table will never be in RAM and so how will queries like select * from table where shortUrl = x execute
Will a part of index table be pulled out everytime to check the location ?
4. In case where this table is very huge say 3 TB. How big will index table be...
5. If index table is larger than size of RAM and since then the queries will take a lot of time. Is there a better alternative ?? Like using noSQL database. or storing data in two machines splitting them rather than on one machine ?