Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files drastically very very soon. I am investigating for quick solutions to release the pressure.

One option is to move the file's content (UTF8 text) into a database and perform SELECTs to replace the file search (by name). The select statements would be the following:

SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE 'criteria%';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%othercriteria';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%andanothercriteria%';

We are talking about a total of 200K requests per day on a 800K rows table (which I could easily split in two if it helps). MyContentName is part of the key and will be indexed. There is either one entry matching the criteria in the table or none.

I am no DB admin expert. Is this something a MySQL instance on a shared server can support or are my expectations too high?

I know the typical answer is: I should test. Unfortunately, because of the emergency, I don't have time to perform tests. I need to find a quick solution, even if temporary, even if it degrades service response delays a little.

I am looking for experienced DB admin's opinion on this strategy. Tips and suggestions are welcome too.

share|improve this question
    
It is late here, I will read answers tomorrow. Thanks. –  JVerstry Apr 10 '12 at 0:28
    
Would you care to explain a bit more about your original situation? What exactly is your problem? Why must you reduce the number of files? I ask because I might have a similar situation, but mine is totally under control. I have a single, small server serving about 15M requests per day randomly distributed among about 2.5M small static files (mainly images, most around 0.6kb - 2.5kb, some a little bigger), and I can assure you that the server load is very, very low: it could handle a lot more. –  Bruno Reis Apr 10 '12 at 6:59
    
What kind of content are you talking about? How are you using this content? What exactly do you mean by small (in bytes)? Is your content mostly read-only, or is it frequently updated? –  Bruno Reis Apr 10 '12 at 7:02
    
Also: what exactly is your "criteria"? Is it really free text? Could you make it somehow a bit more structured? FYI, the first of your queries (like 'xxx%') would run fast if the column is the last part of the used key, while the others (like '%xxx' or like '%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). –  Bruno Reis Apr 10 '12 at 7:08
    
@Bruno Content is UTF8 text in files of about 1k (with some going up to 50K, but a minority). Thanks for the info on 'like' performance. –  JVerstry Apr 10 '12 at 10:15

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

I suggest you should useMyISAM table and add FULLTEXT INDEX if you are observing slowness. Full-text search is the kind of search based on special sort of index (full-text, obviously), Performance is quite optimal in this case While like %% always causes table full table scan which can be terrible slow (when you have 100k and more rows).

You can refer this link: http://www.gammelsaeter.com/programming/mysql-fulltext-search-example/

share|improve this answer

Your Answer

 
discard

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 or ask your own question.