Take the 2-minute tour ×

I have a table I want to store filepaths in - so I have a varchar field, with a size of 4096 (the default MAX_PATH size in linux). However, I need to be able to do queries of all filepaths within a certain directory, so I was thinking of doing a query like:

FROM files_table 
WHERE files_table.path LIKE "/my/awesome/dir/%"

When I run this on my database, with the path field UNINDEXED, it takes about 10s. Ok, I can see it taking a while, considering my table size is about 4 million, and it's an unindexed field. However, when I index it, with an index size of 500, the query time jumps... up to about 30s!

This seems very confusing to me. Does anybody have any ideas on what might be causing this?

For those hungry for more data:

As a bit of extra data - I tried running an "explain" on the query, and found that it IS, indeed, using my index but it reports the key_len as only 5! This seems strange, as well.

Also - while I would like to hear a good answer to my question (because I want to understand what's going on here!), I'm also open to ideas of the, "I dunno why it's doing that, but it doesn't matter, because you really should be designing your database like this..." sort. For those who lean that way, what I'm really trying to do is build a database structure to do queries of various (cached) data from a large networked filesystem. I know that just storing a filepath is probably the most naive way of approaching this, but I figured I'd try it out as a first-pass implementation, and see where it got me.


So, a bit more digging / information: the actual index is a multiple-column index - the first index is an int, holding a batch_id (ie, the table holds cached information about the filesystem, so each snapshot gets it's own batch_id), and the second is my partial index for the path varchar. So when EXPLAIN says the index key_len, the first 4 bytes of that are actually for the batch_id - meaning it only has a one-byte index for the path!

Oh, and the "actual" query does restrict on batch_id too, so it looks more like this:

FROM files_table 
WHERE batch_id=5 
  AND files_table.path LIKE "_globalSoft/my/awesome/dir/%"

Secondly - a large-ish percentage of the files in my database have a path that starts with "_" - "_globalSoft" in the query above being an example of one. (Yes, the paths are all relative.) So, if the key_len is only 5, it's possible that the only character being used in the key is the leading "_" - which would explain why it's so slow.

Of course, this still begs the question of why it's only using the leading "_". When reading the docs for MySQL indexes (http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html), I noticed this line:


Unfortunately, the given link doesn't say anything about string prefix-compression, and I'm having a hard time finding much information about it. What information I have found is all about MyISAM, and I'm using InnoDB right now. (Though switching to MyISAM might make sense, since it's supposedly better with strings.)

share|improve this question
Not sure what you mean with "partial index". That term usually relates to indexes that do not cover all rows of a table (which is something MySQL does not support) - it's not usually used to describe an index that only indexes part of the column values. –  a_horse_with_no_name Aug 6 '12 at 18:36
He meant prefix index, which is an index that covers the first N characters of a CHAR/VARCHAR/*TEXT/*BLOB column. create index my_index(some_field(50)) for example, to index the first 50 characters of some_field. –  Justin Swanhart Aug 7 '12 at 7:00
add comment

3 Answers

The query is slower because MySQL will have to do more IO overall. The index only covers the first 500 characters, and the first 500 characters are not very unique. For a prefix index, MySQL has to match the prefix, then fetch the row to examine if the full value matches the prefix value. For common prefixes, this could generate tons of extra random IO. Random IO is significantly more costly than sequential IO. Without the index, a single pass of the table using sequential IO is done, and the query is faster.

You probably don't want to use MySQL for this type of search. Look at Sphinx, Solr or another text indexing technology, and index the paths using "/" as a word separator.

You could also break the table into N smaller tables and do a full table scan over the N tables in parallel.

share|improve this answer
This make sense, as a general explanation, but I'm not sure it fits my situation. For one, the first 500 characters ARE unique (the longest path i had was 405 chars - the 4096 is to ensure I can handle any valid path on my filesystem). Also, it SHOULD be able to detect a match using the index alone, since the pattern size is far less than the size of the index. Finally, this explains why single accesses through and index are slower - but the index should mean it has to far less accesses/comparisons. My queries are returning only a few hundred, far smaller than the 4 million total size. –  Paul Molodowitch Aug 6 '12 at 17:38
MySQL will still have to consult the table, even if the search string is less than the index prefix length. –  Justin Swanhart Aug 6 '12 at 19:24
add comment

How many records are returning? It appears you're probably returning a substantial percentange of the records; and it's clearly more efficient to scan the data in one pass than to have to pick them out one by one from an index.

Oversimplifying a bit, using an index usually involves three (actual of cached) read tasks. One to find the value in the list of sorted keys, which provides the key to the record in the primary index; one to look in the primary index to find the record position in the table; one to find the record in the table.

Also, google for "cardinality" and see how well your data and indexes qualify.

share|improve this answer
add comment
up vote 0 down vote accepted


Ok, I'm an idiot... the problem was that I was matching directories like "globalSoft" - ie, directories that start with an underscore - and didn't realize that "" was a special character (like %), and didn't escape it.

Forgive my stupidity!

share|improve this answer
add comment

Your Answer


