Basically what I wanted is the opinion from someone experienced using indexes in big tables (10GB~20~30GB); I am testing MySQL, so far the test table I created reached 1.2 GB, now I can tell/feel how an index(primary key,auto increment) will increase performance for a SELECT query. The table has 10,000,00 rows, a SELECT without index (full scan) takes about 12-18 seconds with an index query the time is less than 1/4 of a second.
On the other hand, I have read that the indexes will slow down the speed of writing queries (INSERT, DELETE, UPDATE), since every time a record is changed the indexes must be updated (I understand that a solution for this issue could be setting up replication and redirect all your write queries to the master and read ones to the salves ). So, Does indexing advantages overcome writing disadvantages ?
Also, what if it is a complex query that required a comparison in a non-index column in a 30GB table? You executed and go to lunch ?
Anyone out there managing a big table/DB?