If you are not able to fine tune the filesystem (for instance, by using a smaller block size), and you really must use a database, I'd suggest the following readings:
The first will explain the data structure most commonly used by indexes, the B-Tree. The second explains how MySQL uses the B-Tree. The third will tell you about the command EXPLAIN SELECT ...
, which is how MySQL describes the query plan (it will tell you which (if any) index it is using, if it is doing table scans -- which you must avoid at all costs).
To create an optimized index, you should first think about the structure of the query (or queries) you will need. For example, it might be something like: select content from files where firstParameter = XXX and secondParameter like 'xxx%'
.
You should analyze the cardinality of each column (ie, how many different values can the column have).
You chose the columns with highest cardinality to be the first in an index, leaving those with lower cardinality to the end. Example: suppose you have 2M rows and firstParameter
is a number between 1 and 1M, randomly distributed, and secondParameter
is the full name of the owner of the file. In such a situation, you want the index (firstParameter, secondParameter)
, in this very order, since the clause firstParameter = XXX
will leave you, in average, with only 2 rows. The cardinality of secondParameter
, on the other hand, is much lower: there are much less then 1M possibilities for people's names. Therefore, if your index is (secondParameter, firstParameter)
, the query where firstParameter = 1 and secondParameter like 'bruno%'
will first look for every row in which secondParameter
begins with bruno
(which will be likely tens or hundreds of thousands), and only then it will look for the other condition.
Also, note that the index is used from left to right. That is, if you have 3 columns, A
, B
and C
and you index (A, B, C)
, the index will be mostly useless in a query such as where A = 1 and C = 2
. It will likely be used to find rows matching A = 1
, but after that every row will be checked for C = 2
. If most of your queries are like that one (and some might specify B as well), than your index should be (A, C, B)
.
Finally, note that like 'xxx%'
can use an index, while like '%xxx'
(or like '%xxx%'
) cannot. This is, again, because the index is read from left to right. To match xxx%
, it knows where to start looking; to match %xxx
it must check every single row.
All that said about indexes, I'd strongly suggest you to rework your criteria so that you have something more structured. As you said, you could try to precompute something.
There are other considerations, such as the size of the content. If you can make it fit in under 8KB (which is like 3000 characters if you use UTF-8), then InnoDB will store the data in the same page as the primary key; otherwise, it will store the data elsewhere. If you query by primary key, in the first case you have a single read operation; if you query by another index, in the second case you have 3 read operations: one to find the primary key of matching row, one to find the row by primary key (to read the address of the data) and one to read the data.
Oh, check the amount of RAM of your server. Ideally your data (or at the very least your indexes) should fit in the RAM.
By considering all these points, you should have no problem at all: I don't know the hardware of your server, or its load (since you said it is shared), but 800k rows is close to nothing if you fine tune your indexes; I'm very far from expert and, by doing all the above things, I work daily with (very optimized) tables with 10M, some 100M rows, and the queries are ultra-fast.
I hope that helps. Once you have your table(s), you could ask another question showing the create table
statement and describing a bit about your data (sizes, cardinalities, etc) and the select queries you will use, so someone could help you to create an optimized index.
like 'xxx%'
) would run fast if the column is the last part of the used key, while the others (like '%xxx'
orlike '%xxx%'
) would run slow (in the worst situation -- without more conditions, as in your examples -- you would have a full table scan, which would likely kill your server).