MariaDB - Query optimizations

MariaDB - Query optimizations

Overall articles full of good information about query optimizations:

Very interesting about COUNT DISTINCT

Use Subqueries to Count Distinct 50X Faster Count Distinct Compared on Top 4 SQL Databases

InnoDB Slow Counting

Innodb is very slow in COUNT() queries, compared to MyISAM? Well, remember that InnoDB is not slow for ALL COUNT() queries but only for very specific case of COUNT(*) query without WHERE clause.

Speed up counting methods

  1. Hint engine for the right index to use. Just pick an index that will never contain a NULL value, and tell MySQL to use index (index_name).
    Nice one: Fast MySQL InnoDB count. Really fast

  2. Using estimate from show table status;. Very inaccurate, but viable.

Too much temporary tables created on disk?


SELECT LENGTH(meta_value) AS l FROM etkwp_postmeta ORDER BY l DESC LIMIT 10;
SELECT COUNT(*) FROM etkwp_postmeta;

ALTER TABLE etkwp_postmeta MODIFY meta_value VARCHAR(5120);

5*1024 = 5120 = 4096 = 4.8828125


field: meta_value

Streaming MySQL Results?

Also known as: unbuffered queries


MySQL Optimizations Unbuffered queries for large data sets | the professional weblog

MySQL requires that, inside one connection, a query is completely handled before the next query is launched. That includes the fetching of all results.

Asynchronous MySQL queries?

PHP asynchronous mysql-query - Stack Overflow

Adding indexes


USE saveup_ch;

ALTER TABLE wp_options ADD INDEX autoload(`autoload`);
# Better: ALTER TABLE wp_options ADD INDEX myindex_name(`autoload`);
# Check duplicate indexes: SHOW CREATE TABLE wp_options;
# Show warnings: SHOW WARNINGS;
# Remove index: ALTER TABLE wp_options DROP INDEX autoload_2;

ALTER TABLE wp_ratings ADD INDEX (`rating_postid`);


USE fantrikot;
ALTER TABLE wp_options ADD INDEX (`autoload`);
ALTER TABLE wp_usermeta ADD INDEX (`umeta_id`);


USE gernech;

# for import plugin speedup

ALTER TABLE etkwp_pmxi_posts MODIFY unique_key VARCHAR(100);
ALTER TABLE etkwp_pmxi_posts ADD INDEX unique_key(`unique_key`);
ALTER TABLE etkwp_pmxi_posts ADD INDEX import_id(`import_id`);

ALTER TABLE etkwp_pmxi_imports MODIFY path VARCHAR(255);
ALTER TABLE etkwp_pmxi_imports ADD INDEX path(`path`);

ALTER TABLE etkwp_posts MODIFY post_title VARCHAR(1000);
ALTER TABLE etkwp_posts ADD INDEX post_title(`post_title`);
ALTER TABLE etkwp_posts ADD INDEX post_type(`post_type`);
ALTER TABLE etkwp_posts ADD INDEX post_mime_type(`post_mime_type`);

ALTER TABLE etkwp_options ADD INDEX option_id(`option_id`);
etkwp_options WHERE option_name LIKE '\\_transient\\_%1444981037' ORDER BY option_id

As you might know while running GROUP BY and some other kinds of queries MySQL needs to create temporary tables, which can be created in memory, using MEMORY storage engine or can be created on disk as MYISAM tables. Which one will be used depends on the allowed tmp_table_size and also by the data which needs to be put to temporary tables – BLOB/TEXT columns are not supported with MEMORY storage engine so must use on disk MyISAM temporary table.

BLOB/TEXT columns are not supported with MEMORY storage engine so must use on disk MyISAM temporary table.

!!! !!!

Reduce your server’s resource usage by moving MySQL temporary directory to tmpfs |, Inc. - Drupal Performance Tuning and Optimization, Managed Hosting and Consulting How much overhead is caused by on disk temporary tables - MySQL Performance Blog

The directory /run/mysqld already exists. The old value was show variables like '%tmpdir%';


You can check it is really working a lot with:

apt-get install iwatch
iwatch /run/mysqld/

Somethime even the simple ORDER BY NULL can help immensly.

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL.

Not sure if it still like that in latest MySQL incarnations.

Optimize all tables on all databases on server:

mysqlcheck -o --all-databases

Speed of import:

PHP-FPM, no download: 1000 pro/min HHVM, no download: 2500 pro/min

HHVM is still faster.

post_content, post_excerpt, to_ping, pinged, post_content_filtered

SELECT MAX(LENGTH(post_content)) FROM etkwp_posts;

SELECT MAX(LENGTH(post_content_filtered)) FROM etkwp_posts;

ALTER TABLE etkwp_posts MODIFY post_content VARCHAR(23000);
ALTER TABLE etkwp_posts MODIFY post_excerpt VARCHAR(512);
ALTER TABLE etkwp_posts MODIFY post_content_filtered VARCHAR(512);

ALTER TABLE etkwp_posts MODIFY to_ping VARCHAR(64);
ALTER TABLE etkwp_posts MODIFY pinged VARCHAR(64);

OK, definitivno mogu da smanjim dužinu. Ali, vidi - pa ako nekad mora, neka ide u neki custom field.

WooCommerce Nested Category Layout

Fastest solution for subcategories? Cartesian Product table

mysql - Large database causes slow load - WordPress Development Stack Exchange

Sizes are not the problem:

du -hs /var/lib/mysql/{gernech,saveup_ch}
# result: 701M, 63M


Fast counting of rows on InnoDB • King’ori J. Maina Cloudspace | Fast MySQL InnoDB count. Really fast Read from metrics :)

Fastest way to estimate rows in a table | Master MySQL

SELECT data_length/avg_row_length FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'etkwp_posts' AND table_schema = 'gernech';
SELECT table_rows FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'etkwp_posts' AND table_schema = 'gernech';
SELECT COUNT(1) FROM etkwp_posts;

OMG!!! Crazy!!!

Redudant index!

The “optimize table” didn’t help much, but the redundant index solved the problem. Thanks!

mysql - “SELECT COUNT(*)” is slow, even with where clause - Stack Overflow

InnoDB uses clustered primary keys, so the primary key is stored along with the row in the data pages, not in separate index pages. In order to do a range scan you still have to scan through all of the potentially wide rows in data pages; note that this table contains a TEXT column.

Create an additional non-primary index on just the change_event_id column. This will store a copy of that column in index pages which be much faster to scan. After creating it, check the explain plan to make sure it’s using the new index.

MySQL :: MySQL 5.7 Reference Manual :: Clustered and Secondary Indexes When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

Four ways to optimize paginated displays - MySQL Performance Blog

Use Subqueries to Count Distinct 50X Faster

Mysql – Removing the “Using filesort”

date 01. Jan 0001 | modified 28. May 2021
filename: MariaDB - Query optimizations