SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post How would you perform your search query to search with millions of Data ?

    Hi, I just want to ask ,...How would you perform your search query if you have millions of data in your database...is there a fastest SQL query to perform on this that can find with less time?


    Thank you in advance

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,260
    Mentioned
    196 Post(s)
    Tagged
    2 Thread(s)
    Millions? You're talking hypothetically and don't have a specific example correct?

    Depending on what you're trying to do I imagine there's always a "best" way.

    First important thing is that your table architecture is optimal. eg. "normalized".

    Also, don't use SELECT * explicitly specify only the fiields you need
    And if you're going to get a bucket load of results try using LIMIT and OFFSET

  3. #3
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Millions? You're talking hypothetically and don't have a specific example correct?

    Depending on what you're trying to do I imagine there's always a "best" way.

    First important thing is that your table architecture is optimal. eg. "normalized".

    Also, don't use SELECT * explicitly specify only the fiields you need
    And if you're going to get a bucket load of results try using LIMIT and OFFSET
    Thank you for your reply I haven't tried OFFSET...

  4. #4
    SitePoint Wizard Stomme poes's Avatar
    Join Date
    Aug 2007
    Location
    Netherlands
    Posts
    10,283
    Mentioned
    51 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jemz
    Hi, I just want to ask ,...How would you perform your search query if you have millions of data in your database...is there a fastest SQL query to perform on this that can find with less time?
    Faster than continual SQL queries is an actual search engine: SOLR, Sphinx, ElasticSearch (built on Lucene), etc.

  5. #5
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    First important thing is that your table architecture is optimal. eg. "normalized".
    Hm, I thought we de-normalize db architecture to improve performance, not the other way round?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    Hm, I thought we de-normalize db architecture to improve performance, not the other way round?
    we normalize to eliminate redundancies, prevent errors, avoid inconsistencies, and ensure data integrity

    dude, never skip that step, okay?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    we normalize to eliminate redundancies, prevent errors, avoid inconsistencies, and ensure data integrity

    dude, never skip that step, okay?
    Yep, but the subject is about performance not data integrity. After we got it (integrity) we may find out that some normalizations bite performance too much so we sacrifice some of them to keep the system fast. Isn't that right? I wrote de-normalize, not normalize in my previous post .

  8. #8
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi to all experts,....Can i rephrase my words,...what i want is to search a certain word or string with in database which have millions of data.so how would you guys perform the search query,which is efficient and faster to get the result.

    Thank you in advance, Hope this helps

  9. #9
    SitePoint Wizard Stomme poes's Avatar
    Join Date
    Aug 2007
    Location
    Netherlands
    Posts
    10,283
    Mentioned
    51 Post(s)
    Tagged
    2 Thread(s)
    Full text search and m0aR
    or focus simply on full-text search
    or if already using an awesome database, search may be built right in

    You get queries with filters, fuzzy matching, stemming, and with ES possibly a standalone document store+ if that's all hip and trendy with the kids these days. I have seen ES churn through gazillions of data and it's fast. Eats memory like alzheimers, but fast.


    Okay I'm assuming jemz just wants to know an example query but you'd thing that depended heavily on the data, structure and what you're searching for.


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
  •