Results 1 to 8 of 8

Thread: MySql ResultSet

  1. #1
    Join Date
    Jan 2008
    Posts
    24
    Rep Power
    0

    Thumbs up 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???

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    Default

    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.

  3. #3
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.

  4. #4
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

  5. #5
    SecretCode is offline Member
    Join Date
    Jun 2008
    Location
    South Africa
    Posts
    6
    Rep Power
    0

    Default

    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?

  6. #6
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    Default

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

  7. #7
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.

  8. #8
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    Default

    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)

Similar Threads

  1. ResultSet size
    By bugger in forum JDBC
    Replies: 18
    Last Post: 06-25-2011, 08:36 PM
  2. Replies: 5
    Last Post: 06-26-2008, 03:16 AM
  3. ResultSet to XML
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-14-2008, 09:50 AM
  4. ResultSet to HTML
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-12-2008, 09:32 AM
  5. ResultSet example
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-20-2008, 08:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •