php - When to use MySQL over a filesystem "database" when checking for unique strings
Get the solution ↓↓↓
Note: this is only for the case of checking for unique EXACT MATCH of strings less than 128 char.
A very common operation in many of my apps is simply checking for existence of a persistent string that is either a
- unique username,
- unique filename/URL
- (in general) unique string (UUID for example)
In the past, without thinking much about it, I have just created a standard table in MySQL for keeping track of this.
I recently was told that this equivalent operation of simply checking for a unique string should probably just be done on the filesystem, checkfile_exist() on a path.
My question is, when should one use MySQL vs merely(?) file system?
My naive understanding is that MySQL is designed to index better - but is this only for more complex operations?
I have a few worries:
- Does PHP
file_exists()degrade when there are a large number of files in a folder? At which point should you "shard" a folder on an CentOS or Ubuntu Apache server (actually, I am not sure how dependent on PHP version, OS, web server version this is)? - If one needs to do things like list files, does that also come at a high performance cost over just your typical MySQL
SELECT Like '%bla%'for example?
Lemma: should both systems be created in parallel? The filesystem being used for quick checks and the MySQL system for listing / searches / other operations?
Answer
Solution:
For all those use cases you just need a table that describes each one, and then you can do uniqueness checks against that. The key is to have aUNIQUE index on each important column, like:
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE KEY `index_users_name` (`name`)
);
Where it's now trivial to check:
SELECT COUNT(*) FROM users WHERE name=?
If you're just looking for exact matches. You can also useWHERE IN (?,?, ...) for a list of potential matches.
Note: You would only use a filesystem if these identifiers have large amounts of binary data associated with them. MySQL's BLOB columns require a lot more work for the server to deliver than a file on disk, plus can complicate your backup strategy. Dumping a 10GB database isn't hard, but dumping a 2TB one requires significant effort even if only one file changed.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: 403 this action is unauthorized.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.