which is the best optimizaton method in MySQL

Question

Suppose I have 1 million data in my database. what will be the most optimized criteria to get faster results??

Criteria 1:

Single database - Many tables

Criteria 2:

Multiple database - Multiple tables

Criteria 3:

Single database - Single table

and what are the other factors which I need to take care off??

Thanks

Mathew

Answer 1

One millions rows isn't all that much and then, as always, it depends.

• What kind of queries are you going to run?
• Ratio of select and inserts.
• How big are the rows?

If you still think you need more than a vanilla MySQL table then you could take a look at MySQL Cluster.

Answer 2

The most relevant factors are:

1. The nature of data
2. The information you are going to retrieve

Answer 3

When ever we design the database, consider the following points

1. How much data will you be adding and storing in the database
2. IS there any reports that you need to genarate and stored
3. Try to create database that will be very easy for you to write the Queries
4. Since there are millions of records, make sure all the tables are properly indexed and maintain the proper relationship between the tables.