SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to do horizontal searching in MySQL??

    hi all

    how can we do horizontal searching using Java + MySQL...???

    how can we start traversing the database at a specified row???

    and, how can we stop traversing at a specified row as well???

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there is no such thing as a "specified row" unless you specify the value of the primary key (or a unique index)

    the rows in a table do not have positions

    a table is like a bag full of marbles where each marble is a row, and they aren't in any order

    and what does "horizontal searching" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi all

    thanks r937 for quick reply...horizontal searching (row-based searching) means we do horizontal partitioning (row-based partitioning) of a table in a database.... example: there are 100 records in a table to be traversed...so if we want to have 4 partitions of traversal process so each partition will traverse 25 records only....1st partition traverse record 1 to 25, 2nd partition from 26 to 50, and so on...

    that's why i want to know how to traverse tables in a database at a specified row (1 to 25, 26 to 50, ...) and stop at a specified row as well...or in another word it's enough in my thought if i know how to jump to a specified row....

    i hope you guys can guide me...thanks in advance guys...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "jump to a specified row" makes sense only if you use an ORDER BY clause and then count rows to get to where you want

    in mysql you can use LIMIT, in sql server you can use TOP, etc.

    but don't kid yourself that this is going to be more efficient, it isn't -- each of the 4 queries in your example would have to sort the entire table and then ignore three-quarters of the sorted results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi r937

    thanks for your reply...

    i'll find information about LIMIT that you've mentioned first....thanks a lot for that information...

    but, actually im trying to develop distributed application where there are more than one server and each server contains same database with same records....that's why i need to partition the traversal processes so that i could save time and increase searching performance....meaning to say if i have four servers then the 1st server only need to traverse the 1st quarter of the database, the 2nd server do the next 2nd quarter part, and so on...

    any opinions would be highly appreciated...thanks in advance....

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    You basically ignore all of that in the way that you write the code accessing the database and control it all from the way that you implement the database across the four servers. To the application it shouldn't matter whether the data is all on one server or spread across 100 servers.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    You basically ignore all of that (why???) in the way that you write the code accessing the database and control it all from the way that you implement the database across the four servers.
    hi felgall...thanks for your reply...

    could you please explain more of what you have just mentioned...?? i dont get it...im sorry

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Whether your database is installed on one server or spans four servers should make no difference to the actual applications. That would all be controlled by the way you set up the load sharing between the servers and to the application the database should look like a single copy regardless of how many servers it actually uses.

    You should be setting it all up in the way you configure mySQL to operate so that the four servers appear as one database to the application.

    See http://dev.mysql.com/doc/mysql-ha-sc...-overview.html for an intro to scalability with mySQL.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi guys

    thanks a lot felgall for the information....im exploring it thoroughly now....it looks very good in the introduction......i got to spend some time to read and try it...

    but before that, i've tried to use LIMIT....this what i get....

    My original records (logusn, logpwd):

    111 aaa
    222 bbb
    333 ccc
    444 ddd
    555 eee
    666 fff
    777 ggg
    888 hhh
    999 iii

    when i wrote this:
    rs = st.executeQuery("SELECT * FROM login where logusn='888' limit 0,5");

    System.out.println("Process starts...");
    int bil = 1;
    while(rs.next())
    {
    String logusn = rs.getString(1);
    String logpwd = rs.getString(2);

    System.out.print("\n"+bil+".");
    System.out.println(" User Name : "+logusn);
    System.out.println(" Password : "+logpwd);

    bil++;
    }
    System.out.println("Process ends...");

    result:

    Process starts...

    1. User Name : 888
    Password : hhh
    Process ends...


    and, when i changed the limit to this limit 1,8

    result:

    Process starts...
    Process ends...


    why for the 1st code i got the result in fact i've limited the search from record 1 to 5 only..??? and, why for the 2nd code i didn't get a result in fact the searched data is actually in the range of record 2 to 9...??? any explanation...??

    thanks in advance....

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by awang View Post
    why for the 1st code i got the result in fact i've limited the search from record 1 to 5 only..??? and, why for the 2nd code i didn't get a result in fact the searched data is actually in the range of record 2 to 9...??? any explanation...??
    yes, here is the explanation -- there is no such thing as a record number

    using LIMIT without ORDER BY makes no logical sense whatsoever

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you need to remember the order in which the clauses of your SQL statement are processed: WHERE is processed before LIMIT.

    since your WHERE clause has limited your results to the one row that has 888 in it, using LIMIT 1, 5 returns no rows because it skips the first and only row of your result set.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  12. #12
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    since your WHERE clause has limited your results to the one row that has 888 in it, using LIMIT 1, 5 returns no rows because it skips the first and only row of your result set.
    but why the 1st code yield a result...??

    this what i get using order by (same order as above):

    Process starts...

    1. User Name : 111
    Password : aaa

    2. User Name : 222
    Password : bbb

    3. User Name : 333
    Password : ccc

    4. User Name : 444
    Password : ddd

    5. User Name : 555
    Password : eee

    6. User Name : 666
    Password : fff

    7. User Name : 777
    Password : ggg

    8. User Name : 888
    Password : hhh

    9. User Name : 999
    Password : iii
    Process ends...


    and, when i changed the code to:

    SELECT * FROM login where logusn ='888' order by logusn limit 0,5

    this what i get and still get:

    Process starts...

    1. User Name : 888
    Password : hhh
    Process ends...

    and, i changed the limit to limit 1,5 again this what i get:

    Process starts...
    Process ends...


    still the same result as before...

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    my explanation still applies: WHERE is applied before LIMIT.

    LIMIT has nothing to do with the rows in your table. it only limits the number of results returned from a query.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  14. #14
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks longneck

    so you are telling me that when WHERE is used, LIMIT is nothing right....ok now i get it....

    but how can i partitioning the process just like my original post instead of one suggested by felgall...?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by awang View Post
    but how can i partitioning the process just like my original post instead of one suggested by felgall...?
    change your focus from "row" to "identifier"

    imagine you wanted to launch several asynchronous searches on usernames

    one way to do it would be alphabetically -- one search for the As, another search for the Bs, etc.

    so the individual searches would define their WHERE clauses like this:
    Code:
    WHERE username >= 'A'
      AND username  < 'B'
    Code:
    WHERE username >= 'B'
      AND username  < 'C'
    and so on

    the individual searches will be efficient when there is an index on the "partitioning" column

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    ^^^That was pretty much how MySpace did it. Basically, they had one master account server, which had the account login info and a pointer to which content db the user lived in. User logs in, app figures out which DB to use and the user gets connected to the right DB.

  17. #17
    SitePoint Member
    Join Date
    May 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi all

    thanks for the replies...

    guys, in your opinion which will improve the processing performance, row or identifier partitioning methods...??

    thanks in advance....


Bookmarks

Posting Permissions

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