|
|
Welcome to the Java Forums.
You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community, you will:
- have access to post topics
- communicate privately with other members (PM)
- not see advertisements between posts
- have the possibility to earn one of our surprises if you are an active member
- access many other special features that will be introduced later.
Registration is fast, simple and absolutely free so please, join our community today!
If you have any problems with the registration process or your account login, please contact us.
|
|
06-18-2008, 08:24 AM
|
|
Member
|
|
Join Date: Jan 2008
Posts: 16
|
|
|
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???
|
|
06-18-2008, 09:05 AM
|
 |
Moderator
|
|
Join Date: Jul 2007
Location: Colombo, Sri Lanka
Posts: 3,030
|
|
|
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.
__________________
Use an appropriate Subject. "Help, urgent!" isn't one.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Has someone helped you? Then you can
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
their helpful post.
Want to make your IDE the best?
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Close on September 4, 2008)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
06-18-2008, 11:22 AM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 181
|
|
|
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.
|
|
06-18-2008, 12:08 PM
|
|
Moderator
|
|
Join Date: Jul 2007
Location: Colombo, Sri Lanka
Posts: 3,030
|
|
|
And I think we can make partitions in MySql.
__________________
Use an appropriate Subject. "Help, urgent!" isn't one.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Has someone helped you? Then you can
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
their helpful post.
Want to make your IDE the best?
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Close on September 4, 2008)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
06-18-2008, 12:23 PM
|
|
Member
|
|
Join Date: Jun 2008
Location: South Africa
Posts: 6
|
|
|
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?
|
|
06-18-2008, 02:42 PM
|
|
Moderator
|
|
Join Date: Jul 2007
Location: Colombo, Sri Lanka
Posts: 3,030
|
|
|
Ya, one of a wiki page I see that it support from 5.1. As you said why not test it with an example.
__________________
Use an appropriate Subject. "Help, urgent!" isn't one.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Has someone helped you? Then you can
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
their helpful post.
Want to make your IDE the best?
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Close on September 4, 2008)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
06-18-2008, 04:14 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 181
|
|
|
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.
|
|
06-19-2008, 08:41 AM
|
|
Moderator
|
|
Join Date: Jul 2007
Location: Colombo, Sri Lanka
Posts: 3,030
|
|
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)
__________________
Use an appropriate Subject. "Help, urgent!" isn't one.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Has someone helped you? Then you can
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
their helpful post.
Want to make your IDE the best?
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Close on September 4, 2008)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
