Ok, i want to build a database for customers which has 4 columns and 7 billion rows. I don't even know if mysql can handle this. I need guidance on how to proceed with this (ofcourse i will be hiring someone to do it.)
What i want :
The first two columns are keyword and count. Count is number and keyword is string. Queries can be like :
Select keyword which has a word, does not have, equals or/and has count more than or less than etc.
So basically searchable database. I know about full text but that is for words only. How do I make both columns searchable and fast. I need like thousands of keywords returned in a query within few milliseconds like google auto-suggest?
What i have :
A powerful database - 32 GB ram, AMD opteron 3365 2.30 GHz Quad core (HT), 2 TB SATA (i know i need SDD but in case if this works it would be good).
Can this work if 10,000 users are querying that database?
I have seen the data in tab delimited text file. Can lucene do the trick? I will have to import txt in mysql and then index it? Something else that will work much better ?
Let me know pls. Also if you could tell me some company which does this or someone from here who can help me set it up? It's for a startup with backing so no worries about involved costs.
MySQL won't do. You will spend a LOT of time (and possibly money) to get it partitioned so that it's searchable.
Lucene will do. Grab yourself ElasticSearch, set up sharding and you have the result. You would have to learn a different query language (different than SQL), but it's a small cost for having inverted indexes out of the box.