query optimization how to search millions of record in sql table faster -

query optimization - How to search millions of record in SQL table faster? - Stack Overflow

http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster[08/29/2012 5:03:40 PM]

log in | careers | chat | meta | about | faq

Questions Tags Users Badges Unanswered Ask Question

8 Answers active oldest votes

4

1

I have SQL table with millions of domain name. But now when I search for let's say

It takes more than 10 minutes to get the results. I tried indexing but that didn't help.

What is the best way to store this millions of record and easily access these information in short period oftime?

There are about 50 million records and 5 column so far.

sql query-optimization like

share | improve this question edited May 3 '11 at 23:29 asked May 3 '11 at 23:23

1 What database platform are you using? – mrdenny May 3 '11 at 23:46

feedback

8Most likely, you tried a traditional index which cannot be used to optimize LIKE queries unless thepattern begins with a fixed string (e.g. 'lifeis%').

What you need for your query is a full-text index. Most DBMS support it these days.

share | improve this answer answered May 3 '11 at23:27

feedback

3Full-text indexing is the far-and-away best option here - how this is accomplished will depend on theDBMS you're using.

Short of that, ensuring that you have an index on the column being matched with the pattern will helpperformance, but by the sounds of it, you've tried this and it didn't help a great deal.


How to search millions of record in SQL table faster?

SELECT * FROM tblDomainResults WHERE domainName LIKE '%lifeis%'

OMG Ponies101k 10 84 167

user73706321 2

Igor Nazarenko936 1 5

Will A

event Please read beforeflagging as SPAM (areminder from yourmoderators)– ends in 7 days

Hello World!This is a collaboratively editedquestion and answer site forprofessional andenthusiastprogrammers. It's 100%free, no registration required.

about » faq »

tagged

sql × 92916

query-optimization × 1505

like × 1192

asked 1 year agoviewed 2,427 timesactive 1 month ago

Community Bulletin

Visit Chat

RelatedWhat is the best way to spotthe slowest block of a SQLquery?

Performing an ASP.NETdatabase search withStartsWith keyword

Create optimum query to findrecords that are in only onetable

SQL: Optimization problem,has rows?

Speeding up a SQL query withgeneric data information

PostgreSQL LIKE queryperformance variations

Optimising SQL Query WithMultiple Joins

Is there a tool online tooptimize a stored procedureor inline sql

Speeding up inner joinsbetween a large table and asmall table

which one is a faster/bettersql practice?

http://stackoverflow.com/users/login?returnurl=%2fquestions%2f5876861%2fhow-to-search-millions-of-record-in-sql-table-faster

http://careers.stackoverflow.com/

http://chat.stackoverflow.com/

http://meta.stackoverflow.com/

http://stackoverflow.com/about

http://stackoverflow.com/faq

http://stackoverflow.com/questions

http://stackoverflow.com/tags

http://stackoverflow.com/users

http://stackoverflow.com/badges

http://stackoverflow.com/unanswered

http://stackoverflow.com/questions/ask

http://stackoverflow.com/users/135152/omg-ponies

http://stackoverflow.com/users/737063/user737063

http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster?answertab=active#tab-top

http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster?answertab=active#tab-top

http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster?answertab=oldest#tab-top

http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster?answertab=oldest#tab-top

http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster?answertab=votes#tab-top

http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster?answertab=votes#tab-top

http://stackoverflow.com/users/712746/igor-nazarenko

http://stackoverflow.com/questions/tagged/sql

http://stackoverflow.com/questions/tagged/query-optimization

http://stackoverflow.com/questions/tagged/like

http://stackoverflow.com/q/5876861

http://stackoverflow.com/posts/5876861/edit

http://stackoverflow.com/posts/5876861/revisions

http://stackoverflow.com/users/4197/mrdenny

http://stackoverflow.com/a/5876884




http://stackoverflow.com/

http://stackoverflow.com/users/135152/omg-ponies

http://stackoverflow.com/users/737063/user737063

http://stackoverflow.com/users/712746/igor-nazarenko

http://stackoverflow.com/users/383167/will-a

http://meta.stackoverflow.com/questions/58032/how-does-the-spam-flag-work?cb=1




http://stackoverflow.com/about?mnu=1

http://stackoverflow.com/faq?mnu=1




http://stackoverflow.com/questions/5876861/how-to-search-millions-of-record-in-sql-table-faster?lastactivity


http://stackoverflow.com/questions/954108/what-is-the-best-way-to-spot-the-slowest-block-of-a-sql-query?rq=1



http://stackoverflow.com/questions/959480/performing-an-asp-net-database-search-with-startswith-keyword?rq=1



http://stackoverflow.com/questions/998789/create-optimum-query-to-find-records-that-are-in-only-one-table?rq=1



http://stackoverflow.com/questions/1097873/sql-optimization-problem-has-rows?rq=1

http://stackoverflow.com/questions/1097873/sql-optimization-problem-has-rows?rq=1

http://stackoverflow.com/questions/1489136/speeding-up-a-sql-query-with-generic-data-information?rq=1

http://stackoverflow.com/questions/1489136/speeding-up-a-sql-query-with-generic-data-information?rq=1

http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations?rq=1

http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations?rq=1

http://stackoverflow.com/questions/1668478/optimising-sql-query-with-multiple-joins?rq=1

http://stackoverflow.com/questions/1668478/optimising-sql-query-with-multiple-joins?rq=1

http://stackoverflow.com/questions/2054392/is-there-a-tool-online-to-optimize-a-stored-procedure-or-inline-sql?rq=1



http://stackoverflow.com/questions/2256985/speeding-up-inner-joins-between-a-large-table-and-a-small-table?rq=1



http://stackoverflow.com/questions/2515501/which-one-is-a-faster-better-sql-practice?rq=1

http://stackoverflow.com/questions/2515501/which-one-is-a-faster-better-sql-practice?rq=1



feedback

2Stop using LIKE statement. You could use fulltext search, but it will require MyISAM table and isn't allthat good solution.

I would recommend for you to examine available 3rd party solutions - like Lucene and Sphinx.They will be superior.


feedback

1One thing you might want to consider is having a separate search engine for such lookups. For example,you can use a SOLR (lucene) server to search on and retrieve the ids of entries that match your search,then retrieve the data from the database by id. Even having to make two different calls, its very likely itwill wind up being faster.


feedback

1Assuming that your 50 million row table includes duplicates (perhaps that is part of the problem), andassuming SQL Server (the syntax may change but the concept is similar on most RDBMSes), anotheroption is to store domains in a lookup table, e.g.

When you load new data, check if any of the domain names are new - and insert those into the Domainstable. Then in your big table, you just include the DomainID. Not only will this keep your 50 million rowtable much smaller, it will also make lookups like this much more efficient.

Of course except on the tiniest of tables, it will always help to avoid LIKE clauses with a leading wildcard.

share | improve this answer answered May 4 '11 at 0:19

feedback

13.3k 11 29

tereško13.6k 6 19 35

RHSeeger7,096 12 19

CREATE TABLE dbo.Domains( DomainID INT IDENTITY(1,1) PRIMARY KEY, DomainName VARCHAR(255) NOT NULL);CREATE UNIQUE INDEX dn ON dbo.Domains(DomainName);

SELECT * -- please specify column namesFROM dbo.tblDomainResults AS drINNER JOIN dbo.Domains AS dON dr.DomainID = d.DomainIDWHERE d.DomainName LIKE '%lifeis%';

Aaron Bertrand51.5k 7 27 54

Optimizing SQL query withchecking special rules set

SQL Statement using LIKE

How can I learn to optimizesql queries

SQL Optimization Strategy

How to search for “%” in sqltable-column

SQL Query Optimisation(Direction of ConditionEvaluation)

How to optimize a count SQLquery on a big table

Optimizing SQL query beingslowed by bookmark lookup

Rewriting sql query with anested subquery

What kind of overhead isassociated with multipleSELECT queries in SQL?

SQL Query Optimization: isSELECT * FROM Personsreally that much slower thanSELECT * FROM PersonsWHERE City='Sandnes'?

Optimizing a SQL query findingentries that do not exist intable

SQL, how to check that theresult of a select excludes allresults where a string ismatched from another table

Shortening the sentence tofind a search value

SQL Server statements withIN clauses: how to make themfaster?

http://stackoverflow.com/users/383167/will-a

http://stackoverflow.com/users/727208/teresko

http://stackoverflow.com/users/26816/rhseeger

http://stackoverflow.com/users/61305/aaron-bertrand

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

http://lucene.apache.org/java/docs/

http://sphinxsearch.com/







http://stackoverflow.com/users/727208/teresko

http://stackoverflow.com/users/26816/rhseeger

http://stackoverflow.com/users/61305/aaron-bertrand

http://stackoverflow.com/questions/3232384/optimizing-sql-query-with-checking-special-rules-set?rq=1

http://stackoverflow.com/questions/3232384/optimizing-sql-query-with-checking-special-rules-set?rq=1

http://stackoverflow.com/questions/3654572/sql-statement-using-like?rq=1

http://stackoverflow.com/questions/3735523/how-can-i-learn-to-optimize-sql-queries?rq=1

http://stackoverflow.com/questions/3735523/how-can-i-learn-to-optimize-sql-queries?rq=1

http://stackoverflow.com/questions/5928892/sql-optimization-strategy?rq=1

http://stackoverflow.com/questions/6231744/how-to-search-for-in-sql-table-column?rq=1

http://stackoverflow.com/questions/6231744/how-to-search-for-in-sql-table-column?rq=1

http://stackoverflow.com/questions/6586660/sql-query-optimisation-direction-of-condition-evaluation?rq=1



http://stackoverflow.com/questions/7178849/how-to-optimize-a-count-sql-query-on-a-big-table?rq=1

http://stackoverflow.com/questions/7178849/how-to-optimize-a-count-sql-query-on-a-big-table?rq=1

http://stackoverflow.com/questions/7179346/optimizing-sql-query-being-slowed-by-bookmark-lookup?rq=1

http://stackoverflow.com/questions/7179346/optimizing-sql-query-being-slowed-by-bookmark-lookup?rq=1

http://stackoverflow.com/questions/7870226/rewriting-sql-query-with-a-nested-subquery?rq=1

http://stackoverflow.com/questions/7870226/rewriting-sql-query-with-a-nested-subquery?rq=1

http://stackoverflow.com/questions/8010586/what-kind-of-overhead-is-associated-with-multiple-select-queries-in-sql?rq=1



http://stackoverflow.com/questions/8022811/sql-query-optimization-is-select-from-persons-really-that-much-slower-than-se?rq=1





http://stackoverflow.com/questions/8899327/optimizing-a-sql-query-finding-entries-that-do-not-exist-in-table?rq=1



http://stackoverflow.com/questions/11165973/sql-how-to-check-that-the-result-of-a-select-excludes-all-results-where-a-strin?rq=1




http://stackoverflow.com/questions/11202240/shortening-the-sentence-to-find-a-search-value?rq=1

http://stackoverflow.com/questions/11202240/shortening-the-sentence-to-find-a-search-value?rq=1

http://stackoverflow.com/questions/11738860/sql-server-statements-with-in-clauses-how-to-make-them-faster?rq=1





0Indexes are slowed down whenever they have to go lookup ("bookmark lookup") data that the index itselfdoesn't contain. For instance, if your index has 2 columns, ID, and NAME, but you're selecting * (whichis 5 columns total) the database has to read the index for the first two columns, then go lookup the other3 columns in a less efficient data structure somewhere else.

In this case, your index can't be used because of the "like". This is similar to not putting any where filteron the query, it will skip the index altogether since it has to read the whole table anyway it will do justthat ("table scan"). There is a threshold (i think around 35-50% where the engine normally flips over tothis).

In short, it seems unlikely that you need all 50 million rows from the DB for a production application, butif you do... use a machine with more memory and try methods that keep that data in memory. Maybe aNo-SQL DB would be a better option - mongoDB, couch DB, tokyo cabinet. Things like this. Good luck!


feedback

0You could try breaking up the domain into chunks and then searh the chunks themselves. I did something like that years ago when I needed to search for words in sentences. I did not have full textsearching available so I broke up the sentences into a word list and searched the words. It was reallyfast to find the results since the words were indexed.


feedback

0You can use full-text searching for millions of records... http://msdn.microsoft.com/en-us/library/ms142571.aspx

share | improve this answer answered Jul 4 at 10:22

feedback

Your Answer

Jody1,076 5 16

Scott Bruns1,057 3 5

Dilip1

http://stackoverflow.com/users/10235/jody

http://stackoverflow.com/users/343294/scott-bruns

http://stackoverflow.com/users/1501276/dilip





http://msdn.microsoft.com/en-us/library/ms142571.aspx

http://msdn.microsoft.com/en-us/library/ms142571.aspx



http://stackoverflow.com/users/10235/jody

http://stackoverflow.com/users/343294/scott-bruns

http://stackoverflow.com/users/1501276/dilip



about | faq | blog | chat | data | legal | privacy policy | jobs | advertising info | mobile | contact us | feedback

■ stackoverflow.com ■ api/apps ■ careers ■ serverfault.com ■ superuser.com ■ meta ■ area 51 ■ webapps ■ gaming ■ ubuntu

■ webmasters ■ cooking ■ game development ■ math ■ photography ■ stats ■ tex ■ english ■ theoretical cs ■ programmers

■ unix ■ apple ■ wordpress ■ physics ■ home improvement ■ gis ■ electrical engineering ■ android ■ security ■ bicycles

■ dba ■ drupal ■ sharepoint ■ scifi & fantasy ■ user experience ■ skeptics ■ rpg ■ judaism ■ mathematica

rev 2012.8.28.3842

log in

Name

Email

Home Page

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged sql

query-optimization like or ask your own question.

question feed

site design / logo © 2012 stack exchange inc; user contributions licensed under cc-wiki with attribution required

or

http://stackoverflow.com/about

http://stackoverflow.com/faq

http://blog.stackexchange.com?blb=1/


http://data.stackexchange.com/

http://stackexchange.com/legal

http://stackexchange.com/legal/privacy-policy

http://stackexchange.com/about/hiring

http://engine.adzerk.net/redirect/0/2776/2751/0/4de3c60f719c4dfcb1a57531c7050090/0

http://stackoverflow.com/help


http://stackoverflow.com/

http://stackapps.com/

http://careers.stackoverflow.com/

http://serverfault.com/

http://superuser.com/


http://area51.stackexchange.com/

http://webapps.stackexchange.com/

http://gaming.stackexchange.com/

http://askubuntu.com/

http://webmasters.stackexchange.com/

http://cooking.stackexchange.com/

http://gamedev.stackexchange.com/

http://math.stackexchange.com/

http://photo.stackexchange.com/

http://stats.stackexchange.com/

http://tex.stackexchange.com/

http://english.stackexchange.com/

http://cstheory.stackexchange.com/

http://programmers.stackexchange.com/

http://unix.stackexchange.com/

http://apple.stackexchange.com/

http://wordpress.stackexchange.com/

http://physics.stackexchange.com/

http://diy.stackexchange.com/

http://gis.stackexchange.com/

http://electronics.stackexchange.com/

http://android.stackexchange.com/

http://security.stackexchange.com/

http://bicycles.stackexchange.com/

http://dba.stackexchange.com/

http://drupal.stackexchange.com/

http://sharepoint.stackexchange.com/

http://scifi.stackexchange.com/

http://ux.stackexchange.com/

http://skeptics.stackexchange.com/

http://rpg.stackexchange.com/

http://judaism.stackexchange.com/

http://mathematica.stackexchange.com/

http://www.peer1.com/stackoverflow

http://www.peer1.com/stackoverflow

http://stackoverflow.com/users/login?returnurl=%2fquestions%2f5876861%2fhow-to-search-millions-of-record-in-sql-table-faster

http://stackexchange.com/legal/privacy-policy

http://stackexchange.com/legal/terms-of-service




http://stackoverflow.com/questions/ask

http://stackoverflow.com/feeds/question/5876861

http://creativecommons.org/licenses/by-sa/3.0/

http://blog.stackoverflow.com/2009/06/attribution-required/

http://stackoverflow.com/feeds/question/5876861



query optimization how to search millions of record in sql table faster -

Documents