Home > mysql > Responsible use of Indexes

Responsible use of Indexes

September 23rd, 2008

At several stages, I have been asked to benchmark web applications. I always tend to do the light weight stuff at the start, and when it gets into my mind, that the system which is being tested is working some what like it is expected, I steer my course and go for bigger feats. For instance the RatingHQ, was run with a test of hundred thousand registered users and about the same amount of items in each of the sections before releasing for public beta.

Having said the above, the most important matter is that seldom do the programmer check his / her code more than the unit or functional tests. And code thus created would crumple when subjected to higher loads. Several times I have seen code written which simply works, but will break, and take the server that hosts it too down to it knees.

Most of the time, but not always, poor performing code when benchmarked drill down to either having no database indexes or a bit too bloated indexing. In some extreme cases, I have seen theoretical database designs, which follows the non redundancy laws ie theoretically normalized database structure, which fails when performance is of primary concern. In some, the problems were that it was having only the primary key, that too an auto number. And the field type selections, for holding a product, or item title, coders do tend to make the field type text, which in mysql tends to be an overhead.

Sensible distribution of indexes and data would always be good for you. Partial indexing is a very apt choice when the indexes would be having a high cardinality. Storing timestamp as varchar and having a partial index would serve the purpose of storing some activity logs, etc. Whereas datetime would not permit a partial index, and though reads would be fast, writes would be really slow. Keeping data table, and a shadow table moving from shadow to data table would do better if your user registration or product publishing needs acceptance or approval. This is better than having a flag, since your live site would be searching only in the compact approved table.

Having got the chance to wade through and dive deep into code written by programmers known, as well as unknown to me, had the privilege of seeing a lot of code, different approach, methods etc. Taking active (read only) participation in groups, and discussions also has helped me a lot.


Categories: mysql Tags:
Comments are closed.