You are here

index

InnoDB full-text index corruption

In a recent customer engagement we experienced a lot of corrupted (normal) indexes and also corrupted full-text indexes on InnoDB tables in a Galera Cluster (v10.4.13).

In the error log we did not see which table it was but we have only a few log entries every here and there indicating a full-table index is corrupt:

Undefined

MariaDB indexing of NULL values

Taxonomy upgrade extras: 

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

Find evil developer habits with log_queries_not_using_indexes

Recently I switched on the MariaDB slow query logging flag log_queries_not_using_indexes just for curiosity on one of our customers systems:

Deadlocks, indexing and Primary Key's

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

Canias optimizations

Canias is a great ERP product but there is still some potential to improve it. We only focus on MyISAM/InnoDB installations of Canias v6.0.2 in this article. For other DB back-ends those recommendations might be obsolete.

A general problem is, that in InnoDB Primary Keys are crucial in MySQL. Canias often lacks a Primary Key. In this case InnoDB will use the Unique Key for the Primary Key.

In InnoDB data are sorted by the Primary Key so it is a good idea to have the Primary Key on CLIENT and COMPANY as well to get proper sorting of the rows.

Undefined

Impact of indices on MySQL

Taxonomy upgrade extras: 

It is generally well known that indexes help a lot to speed up database queries (especially SELECT but also UPDATE and DELETE). It is less known, that indexes also have some disadvantages.

One of these disadvantages is, that indexes require space in memory and on disk. An other disadvantage of indexes is, that they slow down DML statements like INSERT and DELETE.

We often see at our customers that they do not realized this behavior. Now I found the time to show the impact of (too) many indexes graphically.

Subscribe to RSS - index