Search filenames in MySQL database table restricted by filetype?

I have a MySQL database that I replicate from another server. The database contains a table with this columns

ID, FileName and FileSize

In the table there are more than 4'000'000 records. I want to make fast a search in FileName (varchar) column

I found that I can use for this Sphinx search engine. The problem is that I want to restrict searches by filetype. Do I have to and how (trigers?开发者_运维百科) to extract file extensions for all rows? May be I have to create another table (because this one is replicated) and join them in 1:1 relation?

Can you give me some advices please :)

You'll definitely want to separate the file type into a separate column - this will allow you to direct a Sphinx search on that term (with the match mode set to extended):

normal query terms @filetype jpg

However, this is not exact matching - it will still use Sphinx's intelligence around word matches and such. This may be what you want, but if you don't, I recommend normalising your db and putting file types in another table, and having a foreign key in your files table. Then, you can use this integer as an attribute in your Sphinx setup, and filter on it using the particular file type's id. This will be faster and more reliable in Sphinx, but it is a bit more work.

Why not use an SQL query with a Regex?

Make it something like...

.$query.\.$extension

That would wildcard match the search term into the filename, then a period, then the extension name.