Mysql – Do MySQL optimizations hold for MariaDB also

My rules of thumb for indexing are to

• first, read the execution plan,

then think about adding (or verifying) an index on

• every column used in a WHERE clause,
• every column used in a JOIN condition,
• every column used in an ORDER BY.

In some cases, one multi-column index will be faster in SELECT statements than several single-column indexes.

Then, make sure your WHERE clauses are sargable. Expressions like LIKE '%user: john.doe@example.com%' are not sargable. Unless you extract the user information and store it elsewhere, full table scans are in your future.

When MySQL has to use a filesort, it writes rows to the sort buffer using a fixed-width format. The wider your columns, the longer it's liable to take to sort.

Filesort uses a fixed-length row-storage format similar to that used by the MEMORY storage engine. Variable-length types such as VARCHAR are stored using a fixed length.

The fixed length for columns of type text defaults to 1024 bytes. MySQL will use a slower algorithm for sorts that include text columns.

[MySQL] normally uses the modified algorithm except when BLOB or TEXT columns are involved, in which case it uses the original algorithm.

[snip]

One problem with this approach is that it reads rows twice: One time when evaluating the WHERE clause, and again after sorting the pair values. And even if the rows were accessed successively the first time (for example, if a table scan is done), the second time they are accessed randomly. (The sort keys are ordered, but the row positions are not.)

Both DBAs are right, in a way. There are no limits on the number of schemas, except those imposed by the filesystem, as in MySQL/MariaDB, a schema is basically a directory, producing absolutely no difference for both MyISAM and InnoDB if a table is part of one schema or another in terms of performance or features (you can join 2 tables in different schemas as fast or as slow as if they were in the same schema). I've managed instances with hundreds of thousands of schemas, and the multiple directories were not the performance bottleneck. It is a pure identifier thing. While you can run into some issues, those are indirect to having more or less schemas: for example, in 5.6, a slave can apply changes in parallel only for rows from different schemas.

Why do I say that both are right, in a way? Because having too many objects in the database is a very common source of issues. When you have lots of databases, you usually have 10x or more the number of tables, and that may mean thousands of file descriptors open (that requires memory), thousands of data dictionary cache entries, millions of open table cache entries, etc., which can lead truly to a CPU and above all, memory, bottleneck.