My issue is identical to: https://stackoverflow.com/questions/...extremely-slow

The solution is either to use myisam or upgrade to 5.7. (insert ...duplicate command isn't right for my situation.) I don't know what's different between innodb and myisam, but I'm told to use innodb because something technical. But if myisam is faster, shouldn't I use that one? Why ride a snail when you have a horse?

Anyway, I'm wondering if slackware-current's mariadb has this issue? I can't tell by the file name, since it doesn't use Oracle's versioning.

I don't know what MySQL/MariaDB is good for other than wasting precious time on it, pretending to use a relational DB, as I myself did a long time ago, by the time PostgreSQL was a little difficult to build, to tune and a lot of people called it "way too complex", BullS..t.
MySQL became popular as a small DB for storing webpages data and simple tables, part of the LAMP bundle.
As far as I remember, MyISAM was the default engine MySQL started with, not ACID compliant (thus cannot be called a database, but a trivial form of organizing data), didn't support transactions and very fast because of these "handicaps". Then, recently, the slower innoDB was introduced as an alternative engine, ACID compliant and supporting transactions, helping MySQL to become and be called a DB.
You can start with this article (and the links contained) if you'd like to learn more about these two engines:
https://stackoverflow.com/questions/...sam-and-innodb

MariaDB has some more improvements over these two engines, but I cannot tell you if these improvements are faster or not, as I'm not even installing it in Slackware:
https://mariadb.com/kb/en/library/ch...torage-engine/

Depending on your use case of DB, or if you'd like to get serious about databases, learn (standard) SQL and use a true&professional open source DB, then I suggest moving to PostgreSQL. You'll thank me later.
https://slackbuilds.org/repository/1...em/postgresql/

4 members found this post helpful.

https://mariadb.com/kb/en/library/ma...compatibility/

According to that link, no.

EDIT: I haven't used PostgreSQL for ~3 years for anything special, but you will have to run a job to purge deleted records from your tables every so often. Even so, I'll agree with @abga's assessment of the two databases; PostgreSQL was the DB we migrated to at work after deciding the Oracle was just too expensive for our use case. It was not possible to migrate to MySQL since it did not support database triggers at that time (assuming that it does so now).

The REPLACE INTO is (in)famous slow since Ice Age in MySQL.

AND generally speaking I seen its usage being considered a quite bad habit which slowdowns considerably the sites.

The real solution is to use general compatible queries like "INSERT INTO <blah blah> ON DUPLICATE KEY UPDATE <blah blag>" and call a day.

5 members found this post helpful.

With PostgreSQL 8.1 (2005) the AUTOVACUUM daemon was introduced, it's automatically started and you can tune its vacuuming frequency/schedule.
In addition to the lack of some functionalities, MySQL/MariaDB doesn't adhere to standard SQL and that's a big PITA if you need to export its code / import other standard SQL code in it.

1 members found this post helpful.

Ah, that's good to know!

Well, there's MySQL unique stuff that people can (and do) use, which leads to the PITA you mention. (Or "vendor lock-in" as others call it.)

@hedron
Following Darth Vader's suggestion, you should first consider substituting your REPLACE INTO statement with INSERT ON DUPLICATE KEY UPDATE
https://mariadb.com/kb/en/library/replace/
https://mariadb.com/kb/en/library/in...te-key-update/

Here are some references about this recommendation:
http://code.openark.org/blog/mysql/r...to-think-twice
https://stackoverflow.com/questions/...extremely-slow
And a practical example:
https://thomashunter.name/blog/mysql...te-key-update/

As for choosing the right DB engine, or storage engine, with MySQL/MariaDB, based on my previous post, I can advise you to:
- use MyISAM for when you just want to load some data and run some ad-hoc analysis on it, as fast as MySQL/MariaDB can do it.
- use InnoDB for when you need an actual database and care about your data, be ACID compliant, like to support transactions and some other extensions. This is definitely slower.
https://en.wikipedia.org/wiki/ACID
& again, you might want to consider PostgreSQL

@Richard Cranium
https://mariadb.com/kb/en/library/replace/
"REPLACE is a MariaDB/MySQL extension to the SQL standard." ... useless as it turns out, or as you say, just for the purpose of "vendor lock-in"

2 members found this post helpful.

I did not consider that. "REPLACE INTO" is very convenient, I didn't realize it was "unique" to MySQL/MariaDB. I will consider postegreSQL and try out "INSERT...DUPLICATE" even if it feels really awkward.

I'm really an SQL newb, and was actually considering SQLite, since what I'm doing isn't really the most demanding of tasks, and almost could be done with a spreadsheet, but the files would just be too big.

Nope, is not something very convenient (appearances excluded), but a crap extremely prone for dubious errors.

Because it removes the old record if exists and insert always a new one. That will mess with the primary keys and will eventually produce many dubious errors.

For example, imagine a site where the user "admin" has the primary key "id" "1", and a field "realname"

Usually it is supposed that the IDs stay constant, and many sites use things like
BUT, with that goddamned "REPLACE INTO" when you update the "realname" as "John Doe", the record will be deleted and be inserted one with "id" 666 or whatever. And the site go nuts.

It is just like in Linux, as if when you update the root password, then its UID suddenly become 1755. That will be pretty pretty bad. It will not be root anymore, but some user with elevated access.

Long story short, the "REPLACE INTO" is not "a convenience" but right on another name for "big troubles".

2 members found this post helpful.