I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually the searches will each query multiple match against statements as I will need to search multiple columns in the table index. The only write queries will be to update a single field of a single record approximately 10 to 20 times per minute.
My question is this... How beneficial would it be to setup an 8 drive NVMe raid 0? I know indexes are usually queried from RAM as this is usually significantly faster than storage drives. But with the newest PCIe 4.0 NVMe SSDs and the crazy speeds of these drives, I could probably setup raid0 that has about twice throughout as the fastest DDR4 ram.
Or does none if this even matter? Is throughout the factor that even matters? Can you even force indexes to be queried from the storage drive?
Thanks in advance for the advice.