Do indexing advantages overcome their advantages ?

[angelcool]

Do indexing advantages overcome their advantages for large tables?

[angelcool]

-disadvantages

[requinix]

That's a pretty vague question.

 

How large is the table? What types of indexes does it use? Which advantages and disadvantages matter to you?

[angelcool]

 

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?

 

[Zane]

This forum is pretty large and I'd say the DB is just as large too if not larger... and although I don't manage it, I can guarentee you that it uses indexes.  To not use indexes in a large DB would be insane IMO.  Without indexes, how would you JOIN other tables in your query.

[fenway]

Did you post this question before or after your other thread?

 

But assuming you don't over-index, you'll be fine.