MySql ResultSet

Hello all,

I am using mysql with java to fetch the data from the data base.
I have some three tables with lakhs of rows in it.One of the columns being year.
I am planning to shorten the tables by dividing the tables according to year, without the year column..

Does it take less time to search if I do so???

for example I am doing as

select * from table where year='1990'; (where table has 100000 rows)

I want to do it as

select * from table_1990; (where table has only 1000 rows)

Does it effect in anyways???

Obviously if you have less number of rows to search, it's fine. Because you have to involve with less time to search. But that doesn't mean performance wise there is an improvement.

Create proper indexes and you greatly improve your performance without the "hack" of making multiple tables. Now, if MySQL has partitions (I'm not sure, but I don't think it does), that would be another story.

And I think we can make partitions in MySql.

At least from MySQL 5.1, partitioning is supported. I don't think it is in 5.0.

But why not make a test for this? Do you have a MySQL server under your control, or a test database you can play with?

Ya, one of a wiki page I see that it support from 5.1. As you said why not test it with an example.

If it does have partition, great. The last time I worked with MySQL extensively was a version 4.x, I worked briefly with a 5.0, but nothing later, and always (in those 5.0 projects) on things small enough that partitions had never come into question, so I never really looked into it from that point on.

I never comes with such a situation, because all tables up to now doesn't have large number of data rows. ;)

Even we don't have partitioning directly, I think it is possible to do it through some queries. Basically query less number of rows at a time and do it until end of the table(looping)