I have a table with more than 40 million rows and I want to know what is the best way I can optimize it for searches. The search will happen by the person's document number, and these document numbers are composed by nine numbers from 100000000 to 999999999.
My solution was to split this table into other tables identified by the two first numbers of document that those tables will store, something like, table_10 (documents from 100000000 to 109999999), table_11 (documents from 110000000 to 119999999), table_12, table_13, ..., table99. And when the user searches for the document 10... I will build the SQL to search in table_10.
I'm just a mortal programmer not experienced with large databases, and I don't know if my solution is good, but this is the only solution to make the system faster that I have found. Do you have other recommendations?