MyISAM vs InnoDB vs Anything

Question

MySQL

Which data store is better in the following situation?

The social voting application has an AJAX script that is called about 10-50 times per second. It performs the following actions:

- SELECT by primary key
- REPLACE by unique index
- SELECT with JOIN from two tables and sorting. At the same time, LIMIT 1. Specifically, here:

            SELECT p.*, COUNT(g.id)>0 AS my_votes, p.region='{$reg}' AS from_my_region
            FROM person AS p
            LEFT JOIN guess AS g ON p.oid=g.person_oid AND [email protected]
            WHERE p.age>0 AND p.oid<>@myOid AND p.gender<>'{$gender}'
            GROUP BY p.oid
            ORDER BY my_votes ASC, from_my_region DESC, points DESC, is_app_user DESC, RAND()
            LIMIT 1  

I would formulate it this way:
Of heavy operations, one REPLACE accounts for one two-table SELECT JOIN

There are millions of records in the database, and new ones are constantly being added.
All fields that are being selected are indexed.

Answer 1

MySQL
I would choose

Answer 2

MySQL vs MyISAM Contrasting warm with soft.
It would be more logical to oppose MyISAM vs Innodb
, I would choose MariaDB - there is the Aria engine - MyISAM with transaction support.

Answer 3

MyISAM is much faster, and if you don't mind losing some data, you can use it. BUT... MyISAM locks the entire table on insertion - and under some load, something nasty can happen - requests will queue up. So it needs to be tested.
It is better to use an in-memory data store - Redis, memcache - there will be no problems with inserts. But you will have to rewrite the application, the logic of the storage in memory is completely different.

Answer 4

Most likely, InnoDB will behave better. MyISAM locks the entire table and is only good for light update traffic.