SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    speed up sql - keys

    Hello,

    I'm experiencing slow sql lookup in my product table which has more than 300.000 entries. It takes very for mysql (using php) to lookup for specific entry. I use varchar permalink and int site_id to match the row.

    How can I speed up this lookup?

    Should creating index on 2 columns (site_id and permalink) help?
    If so, for how much should this speed up the lookup?
    What else can I do?

    I tried doing this but I get the following error:

    Code:
    ALTER TABLE `store`.`products` ADD UNIQUE `permalink_index` ( `site_id` , `permalink` )
    
    MySQL said: Documentation
    #1062 - Duplicate entry 'test-product-name' for key 'permalink_index'
    This seems like 2 products with test-product-name exist for the same website, but if I search for this permalink I get no results.

    Thanks a lot for help!
    Get RankTrackr 2.0: Rank Tracker | PopStrap

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    run this --
    Code:
    SELECT store.products.*
      FROM ( SELECT site_id
                  , permalink
               FROM store.products
             GROUP
                 BY site_id
                  , permalink
             HAVING COUNT(*) > 1 ) AS dupes
    INNER
      JOIN store.products 
        ON store.products.site_id    = dupes.site_id    
       AND store.products.permalink  = dupes.permalink
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    run this --
    Code:
    SELECT store.products.*
      FROM ( SELECT site_id
                  , permalink
               FROM store.products
             GROUP
                 BY site_id
                  , permalink
             HAVING COUNT(*) > 1 ) AS dupes
    INNER
      JOIN store.products 
        ON store.products.site_id    = dupes.site_id    
       AND store.products.permalink  = dupes.permalink
    And then delete the entries and make the index key?
    Get RankTrackr 2.0: Rank Tracker | PopStrap

  4. #4
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This query shows 636 total rows // of 389.000

    Should I delete those duplicates?
    Get RankTrackr 2.0: Rank Tracker | PopStrap

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by l2u View Post
    Should I delete those duplicates?
    i wouldn't do that just yet

    first, you should find out why there are duplicates, decide if they are legitimate or not, and do you want to prevent them in future
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i wouldn't do that just yet

    first, you should find out why there are duplicates, decide if they are legitimate or not, and do you want to prevent them in future
    Yes, I definitely do.
    Get RankTrackr 2.0: Rank Tracker | PopStrap

  7. #7
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the best way to delete those duplicates?

    I also want to do prioritization (sort) which duplicates should be deleted..
    Get RankTrackr 2.0: Rank Tracker | PopStrap

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by l2u View Post
    I also want to do prioritization (sort) which duplicates should be deleted..
    can you explain how this would work, to decide which row to keep...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •