Which database to choose: SQLite or mySQL?

Question

PHP

Good day!
There is a working site - fc-dnipro.com.ua . The site was written for myself as a hobby. Written in php, information is stored in text files. Now the understanding has come that it needs to be rewritten using the database, because. firstly, it is not very convenient to fill, and secondly, you need to add registration and a guest book. The question of choosing a database has become ... As far as I understand, one of the advantages of SQLite is the ease of backup (transfer to another hosting) - you just need to copy the database file.
I ask for advice from professionals - will there be enough SQLite capabilities (performance) for such a site, given that it will grow, a guest one will be added, or is it better to use mysql?

Answer 1

Use MySQL. If the site develops, then sqlite will exhaust its capabilities and will have to be transferred again. Mysql backup is done in the same phpmyadmin with one click, and with another click you can recover from the backup.
Hosts, moreover, usually make backups only once a day (sometimes more often). Or they allow you to do this from the admin panel, where you can usually set up an automatic backup.
You will not notice differences in performance at the initial stage of filling.

Answer 2

Sure enough. Even what I need seems to be using just sqllite. Since it contains less bells and whistles and from this it works faster out of the box.

Answer 3

@evnuh Who also has bad advice. This buffering will only be noticeable on large requests. On simple queries, the difference will not be noticeable. The answer was given based on the task. And in your opinion, you can also swear at mysql and say Oracle is better. But why waste money, computer resources, time to work with mysql. If the load on the base in this project is not expected. If, as a result, a herd climbs here, then it seems to me that his site by itself will not cope with the crowd and the database will not help him. It will be of little use if there is shitty code at the top. Didn't mean to offend anyone. I just think that it is necessary not to write bicycles, but to deal with real problems. For his tasks, sqllite is enough. If something grows, then it is better to take ready-made cms.

Answer 4

Thanks everyone for the replies! Decided to choose mysql

Answer 5

MySQL

Answer 6

There is no simplicity of backup with sqlite. This is a binary.
There is a simple option with any database - store the database dump in the version control system

Answer 7

MySQL.
At least due to the fact that the tools for working with it are more convenient.

Answer 8

MySQL...
SQLite is suitable as local storage for mobile devices, but not for websites, especially given the growth.

Answer 9

Don't use sqlite and avoid many problems with locked files, data corruption on power outage.
MySQL with today's engines is much more stable and will be able to hold terabytes of data.
If you want "something unusual" - try nosql, for example mongo.

Answer 10

If the load is expected to be small, then SQLite will suffice.
It will take a bit to figure out the setting for multi-threaded mode, but there is nothing complicated about it.
If the load is high, then MySQL or PostgreSQL is better.
For an HDD with SQLite, I achieved 30 test operations per second (for an SSD - 1000).
In one test operation there were 1 select, 1 update and 1 insert request.
Approximately the same number of times per second they visit Habr on average (if you look at the weekly statistics and excluding peaks).
To administer SQLite, there is an adminer (similar to phpMyAdmin).

Answer 11

I would choose PostgreSQL, mysql does not have convenient storage, and PostgreSQL is somehow more like it.
SQLite is a great solution for small projects, in general you can do it, it is not single-user.

Answer 12

PhpStorm works very well with SQLite and sees the database file as a complete table.