SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    St Petersburg, Russia
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    "order by" optimization

    I can insert rows in random order. Does it mean that if I want the extracted information to be ordered, mysql will sort them each time. In other words does mysql sort the rows, or it sorts indexes. Can I tell it to sort indexes or tell it not to sort indexes, and how much time does it take to sort them?

    Also I know that if I need to extract first 20 rows I can use "limit" keyword. But if I need to extract rows from 100 to 120, how can I do it without accessing first 100 rows?

  2. #2
    SitePoint Member Blue Streak's Avatar
    Join Date
    Dec 2004
    Location
    Sydney, Australia
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    G'day there. I'm pretty sure that MySQL (like most databases) sort the indices - generally it makes more sense because there is less data to sort through in an index (key value + row number, compared to the whole row + row number) and therefore there's less file work to do.

    For the second part, you can use LIMIT with two numbers:
    Code:
    SELECT * FROM my_table LIMIT 100, 20
    Note that the first number is an offset, not your starting row. So this would skip the first 100 rows and grab 20 rows starting from row 101.

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    St Petersburg, Russia
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot. Did not know about this sintax for 'limit'.

  4. #4
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With regard to sorting, you should never rely on SQL to return you the results in the order you want. If you need the results returned in a specific order, add the ORDER BY clasue to your SQL statement.


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
  •