Is storing filename in varchar datatype column good? Is there other way on handling files for your software like creating a folder by numeric Id and just checking if a file exists in the folder in your application logic?
Thanks!
I suggest the file name should be varchar.
Having a numeric id is a good practice generally for performance in mysql. For instance, int max size is 4 bytes. But varchar max size goes upto 9-12 bytes. Varchar also uses different characters sets like utf etc which is why (some other reasons) it takes more space.
If the varchar will be 20 characters, and the int is 4, then if you use an int, your index will have FIVE times as many nodes per page of index space on disk... Which means that traversing the index will require one fifth as many physical and/or logical reads.
It kind of both depends on your length of numeric id and filename char length. And also, having a numeric id might complicate the situation a bit for you. Because even minor changes in numeric values might change file names, or change in filenames might also change numeric values.
And also, remember you should write a script or do something when ever you enter a new file name. Something has to convert the file name to numeric id. If the filenames remains same always, may be a numeric id is a good option.
Finally, I would say it all depends on your application, size of table (no of rows), length of file name, length of numeric id etc.