MariaDB Query Optimization
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
-
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 -
Using estimate from
show table status;. Very inaccurate, but viable.
Too much temporary tables created on disk?
RAZMISLI O PREBACIVANJE BLOB U VARCHAR PRVO IZMERI KOJI JE SAD NAJVEĆI VALUE
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
etkwp_postmeta
field: meta_value
Streaming MySQL Results?
Also known as: unbuffered queries
mysql_use_result
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
save-up:
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`);
fantrikot
USE fantrikot;
ALTER TABLE wp_options ADD INDEX (`autoload`);
ALTER TABLE wp_usermeta ADD INDEX (`umeta_id`);
gernech
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 | 2bits.com, 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%';
/run/mysqld
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
COUNT?
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 :: 14.2.7.2 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