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

[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?

Thank you in advance

[Mittineague]

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

[jemz]

@Mittineague

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…

[Stomme_poes]

@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.

[Lemon_Juice]

@Mittineague

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?

[r937]

@Lemon_Juice

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?

[Lemon_Juice]

@r937

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 :).

[jemz]

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

[Stomme_poes]

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.