SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting first A and than B from the same table

    I have a table advertisers.
    id | companyId | packageType | text | adImage

    I need to display them in search results where advertisers which have packageType=golden are shown before than packageType=ordinary.

    One of solutions is SELECT * FROM advertisers ORDER BY packageType LIMIT $limitMin, $perPage, but the problem is that ORDER BY is very slow. What other options do I have?

    thank you
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,493
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Put an index on packagetype?

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Put an index on packagetype?
    And use integers for the pageType, ordinary=0, golden=1
    Integer comparison is way faster than string comparison

  4. #4
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you. But still I think that can be a very wasting solution because I need to display only 10 results per page and user can never go deeper than 1000 results. By using order by it need to go through all 100.000 rows.

    I was thinking to make two queries. 1st WHERE packageType=1 and 2nd WHERE packageType=2. Than I would calculate with php mysql LIMIT for pagination purposes.
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    You would have to use more queries your way

    - SELECT * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage
    - SELECT COUNT(*) FROM advertisers WHERE packageType=1
    - SELECT COUNT(*) FROM advertisers WHERE packageType=2

    You need the last two to be able to figure out the details of pagination

    Why don't you add the index, convert the column to INT and see how it works with ORDER BY and LIMIT?

    I created a website once that currently has some 180.000 rows in one table, and also uses an ORDER BY on an INT value and a LIMIT, and it's still fast.

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    You would have to use more queries your way

    - SELECT * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage
    - SELECT COUNT(*) FROM advertisers WHERE packageType=1
    - SELECT COUNT(*) FROM advertisers WHERE packageType=2

    You need the last two to be able to figure out the details of pagination
    You could replace
    Code:
    - SELECT * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage
    - SELECT COUNT(*) FROM advertisers WHERE packageType=1
    with
    Code:
    SELECT SQL_CALC_FOUND_ROWS * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage;
    SELECT FOUND_ROWS();
    . That's only on query, sort of anyway, to figure out the pagination details. However your approach would probably be the more efficient one.

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jOOOL View Post
    SELECT SQL_CALC_FOUND_ROWS * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage;
    SELECT FOUND_ROWS();
    Then you don't the number of rows for which packageType=2, so you'd need one more query

    Quote Originally Posted by jOOOL View Post
    However your approach would probably be the more efficient one.
    I'm not sure, SQL_COUNT_ROWS is also really fast.
    I'm hoping the OP will come back to this thread and post his findings, it would be interesting to see which of the suggested approaches is the fastest (although I think it will be a "battle for microseconds").
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •