Designing a database as part of a big project, will need a lot of experience. There are several factors which should be considered before finalizing a database schema. Since once the project starts rollout, there is no going back, other than adding a new index, or adding another field, any major change will affect the whole code base.
Indexing is the most important method you should try first for speeding up queries. Other techniques are also available, but generally the one thing that makes the most difference is the proper use of indexes. On the MySQL mailing list, people often ask for help in making a query run faster. In a surprisingly large number of cases, there are no indexes on the tables in question, and adding indexes often solves the problem immediately. It doesn’t always work like that, because optimization isn’t always simple. Nevertheless, if you don’t use indexes, in many cases you’re just wasting your time trying to improve performance by other means. Use indexing first to get the biggest performance boost and then see what other techniques might be helpful.
One should take absolute care to optimize the fields by keeping in mind, that larger the indexes, slower the operations. The most common example of a mistake is to add a timestamp field to select the data in chronological order. Now if the total rows is just a handful, this wont be a problem atall, but if the total rows is millions, the timestamp field may have a cardinality of say 50% of the total rows, making the index very huge, and operations slow. But if instead of the timestamp, the data is stored in two fields with date in one, and time is another the cardinality of time will be 86400, ie the no of seconds in a day, and the cardinality of date will also be under control, making the operations faster by an appreciable amount. For varchar fields, creating partial indexes instead of full index helps, reduce the size of the index file, and hence give the server breathing space.