Proper indexing can work wonders for large databases in query efficiency, it’s a compromise of disk storage in exchange for sorting speed. For databases with millions of records, indexing takes considerable amount of disk storage, and, AGONIZING lengths of time to create.
I recently compiled a database with one table mytable consisting of over 4 million records, as apparently I want some columns to be identifiable for each of the rows, I need indexing one or 2 of them to do that.
ALTER TABLE mytable ADD INDEX categoryWherein category is one of the columns by which every record in mytable is referenced to that of another table.
You know how long this short query took me?
4 hours. Much longer than the time it took me to do a unique indexing just before it, on the same table.
So there you go, a general indexing takes much much longer time to finish than unique indexing and primary key indexing.
But that’s not all of the tip today.
The tip is that, before you are inserting all the records one by one into that table which would result in a super huge set of rows, configure that particular column to be generally indexed.
This is no better solution than indexing it all after the table is fully populated though, in that with preexisting table indexes, it takes a bit longer to insert every record as MySQL has to write down redundant information every time it inserts a new row into the table.
But in terms of programmer experience, it’s much better than waiting 4 hours hoping for the indexing to succeed and fearing it would not. I’d rather spread the extra required time across each insertion of the records.
Good comments. There are some really good articles on when and when not to index. As a basic suggestion you should only index colums that would be accessed in the where part of a query.
e.g. select name,address,occupation from people where country=’Ireland’ and age>=18;
so here indexing names and addresses would never be useful only the age and country would benefit from indexing.
Thanks for the contribution, Tony.