Execution Time: Finding a File vs. Finding a Row in MySQL

[zekova]

What if I have 100,000 rows in a MySQL table, and a 100,000 files in a directory, each file representing a row in the MySQL table.... 

 

Which, in your experience, is faster to find? A file called 123456789.csv or a record where id='123456789'?

 

Any input is appreciated, thanks!

[jazzman1]

The question is how many requests ( approximately per minute) you're expecting to have to DB server? If the DB server is so busy I suggest to use the application server to call those files.

[zekova]

I guess it's a matter of how fast it goes. The script will run consecutively, so it'll be a lot of requests one after another.

 

So I assume it's best to search for filename instead, thanks jazzman!

[Barand]

Table with over 180,000 records.

mysql> SELECT COUNT(*) FROM votes;
+----------+
| COUNT(*) |
+----------+
|   182685 |
+----------+

Find 100,000th

mysql> SELECT * FROM test.votes
    -> WHERE id = 100000;
+--------+-------+------+
| id     | votes | type |
+--------+-------+------+
| 100000 |   647 |    7 |
+--------+-------+------+
1 row in set (0.00 sec)

Less than 1/100th of a second, so the question is "how long to find and read your file?"

[zekova]

Right, but thousands of consecutive queries might take more mem and cpu power than calling up a specific filename, no? 

[Barand]

So it started as

find one record vs find one file

 

Now its

Find 1000's of records vs find one file

 

If you are going to keep moving the goalposts perhaps you should experiment and do your own benchmark tests