angelcool Posted May 13, 2011 Share Posted May 13, 2011 Do indexing advantages overcome their advantages for large tables? Quote Link to comment https://forums.phpfreaks.com/topic/236363-do-indexing-advantages-overcome-their-advantages/ Share on other sites More sharing options...
angelcool Posted May 13, 2011 Author Share Posted May 13, 2011 -disadvantages Quote Link to comment https://forums.phpfreaks.com/topic/236363-do-indexing-advantages-overcome-their-advantages/#findComment-1215213 Share on other sites More sharing options...
requinix Posted May 14, 2011 Share Posted May 14, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/236363-do-indexing-advantages-overcome-their-advantages/#findComment-1215241 Share on other sites More sharing options...
angelcool Posted May 14, 2011 Author Share Posted May 14, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/236363-do-indexing-advantages-overcome-their-advantages/#findComment-1215290 Share on other sites More sharing options...
Zane Posted May 14, 2011 Share Posted May 14, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/236363-do-indexing-advantages-overcome-their-advantages/#findComment-1215298 Share on other sites More sharing options...
fenway Posted May 15, 2011 Share Posted May 15, 2011 Did you post this question before or after your other thread? But assuming you don't over-index, you'll be fine. Quote Link to comment https://forums.phpfreaks.com/topic/236363-do-indexing-advantages-overcome-their-advantages/#findComment-1215710 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.