MariaDB EXPLAIN same query - different results on different servers

Same data , same queries , different response time. Server 1 is MariaDB version 10.2.6 using the innodb version 5.7.14 Server 2 is MariaDB version 10.3.17 a using the innodb version 10.3.17 There is a VIEW and within the VIEW there is a UNION covering two SELECT statements . The underlying dataset is greater than 100 million rows.Note: Dataset is refreshed twice a year - Since the migration to Server 2 - the index from the table 1 is not used , causing a significant drop is response time for certain queries. The Execution Plan is now forcing a Table... Read more →

MariaDB RLIKE condition

Question: I'm having a problem using RLIKE and searching for a hyphen '-' , within a string.Also some other problems related to identifying strings with both character and numeric , while excluding numeric only or letters only strings. These are some examples of the strings: Example 1 : Digit followed by letter – 12B, 110A , 78B, 2150A, 287H, 941C Example 2: Numbers or numbers/letter with hyphen separator eg 61-63, 98-99, 1-33,2-14,1A-2A,169-173 Example 3: Numbers with forward slash separator e.g 1B/32,142/8 ,336C/2 Could you give me some RLIKE examples for each of the above scenarios Answer: The following are simple... Read more →

MariaDB /tmp file filling with MAD file

Question: I've received an alert from a RHEL server with MariaDB installed. The alert refers to a MAD file filling the /tmp drive . What is going on? and how can I fix the mysql MAD file filling the /tmp drive? Answer: This is a common problem when under certain circumstances - CREATE & ALTER statements executed on InnoDB tables create a temporary table in mysql's tmpdir. To find out the current value check my.cnf or use SHOW VARIABLES LIKE "TMPDIR During the data load phase MariaDB creates a temporary table in /tmp - which consequently fills the /tmp The... Read more →

Quick access to MariaDB Cheatsheet

MariaDB sql cheatsheet - commands I use regularly Read more →