Massive MySQL database [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I'm making a website which requires an absolutely 开发者_如何学运维massive mysql database, with potentially millions or even billions of rows in a single table.

The table structure is two "text" fields.

How can this be optimized for querying? I want to be able to fetch a single row at a time only, but as quickly as possible.

One field might have slightly longer text chunks, but the other one will have a relatively short one at all times, and any row selection will be done based on the other one.

Alternatively, is there a better way to store this many values?

Thank you for your help!

---

So in essence, to fetch a row you will be doing something like:

SELECT * FROM myTable WHERE col1 = "someText";

Is that correct? If so, then the only optimization you can really apply is to add an index on col1.

But, does your data naturally segregate in some way? Are there any logical groups that exist? If so you could break it down into multiple tables and distribute the data between them.

Or if not then you could always segregate based upon something like the first character in col1 assuming that the values here will always be in some reasonable range (like [a-z]). Then you could do things like:

SELECT * FROM myTable_s WHERE col1 = "someText";
SELECT * FROM myTable_o WHERE col1 = "otherText";

Having multiple small tables and choosing between them programmatically will be faster than having a single large table that has everything in it, for very large data sets.

---

the only way to optimize it is to not use text but varchar[size] for your columns but this is possible only if size has a rezonable limit (if it is to big indexing won't be to as effective, and varchar[size] has a fixed size unlike text)

of course the best way would be to use something short for searching, with a table with millions table I can only recommend using int as primary key even if this means you have to add a new column

---

I'm far from an expert when it comes to databases but if you're selecting single rows based on a single field, I'd assume that field might be unique? It might be a good idea to create a hash index on that field although I'm not sure how well that will hold up once with many millions rows.