Database Reference
In-Depth Information
MariaDB is a multiuser multithreaded DBMS; in other words, it often
performs multiple tasks at the same time. And if one of those tasks
is executing slowly, all requests will suffer. If you are experiencing
unusually poor performance, use
SHOW PROCESSLIST
to display all
active processes (along with their thread IDs and execution time). You
can also use the
KILL
command to terminate a specific process (you
need to be logged in as an administrator to use that one).
■
There is almost always more than one way to write a
SELECT
state-
ment. Experiment with joins, unions, subqueries, and more to find
what is optimum for you and your data.
■
Use the
EXPLAIN
statement to have MariaDB explain how it will
execute a
SELECT
statement.
■
As a general rule, stored procedures execute quicker than individual
MariaDB statements.
■
Use the right data types, always.
■
Never retrieve more data than you need. In other words, no
SELECT *
(unless you truly do need each and every column).
■
Some operations (including
INSERT
) support an optional
DELAYED
keyword that, if used, returns control to the calling application imme-
diately and actually performs the operation when there are no more
pending operations. While this improves client performance (as control
is returned immediately), this option does introduce a risk—if a server
were to crash those queries would be lost.
■
When importing data, turn off autocommit. You may also want to
drop indexes (including
FULLTEXT
indexes) and then re-create them
after the import has completed. Alternatively, you can use
ALTER
TABLE
to temporarily
DISABLE KEYS
(remember to
ENABLE KEYS
when you are done).
■
Database tables must be indexed to improve the performance of data
retrieval. Determining what to index is not a trivial task, and involves
analyzing used
SELECT
statements to find recurring
WHERE
and
ORDER
BY
clauses. If a simple
WHERE
clause is taking too long to return results,
you can bet that the column (or columns) being used is a good candi-
date for indexing.
■
Have a series of complex
OR
conditions in your
SELECT
? You may
see a significant performance improvement by using multiple
SELECT
statements and
UNION
to connect them.
■
