MySQL/MariaDB – Do Unused Columns Notably Affect Performance?

mariadbMySQLoptimization

In the WordPress MySQL/MariaDB database there is a table with some columns that in my own uses of WordPress are never used.

The exact columns I am talking about are:

comment_status varchar(20)
ping_status varchar(20)
to_ping text
pinged text
comment_count bigint(20)

None of them is part of an index.

I wonder if removing those columns would make a notable performance difference.

Of course I cannot just remove those columns without modifying the WordPress core. My real goal behind the question is to gain feedback to know if it's worth the effort to participate in the WordPress development and find an alternative way to manage that information for websites still using those columns.

Best Answer

Unused columns do carry a cost, but rarely one that most people will notice or care about in systems with well-written, targeted queries. If you are curious about testing performance and contributing in a meaningful way to the WordPress project, you might want to consider doing something like this:

Make a workable backup of your WordPress database. For example:
```
sudo mysqldump wordpress > wordpress.sql
```
_{Note: This assumes you would create backups via a command-line. If you prefer GUIs, make a backup in your preferred way.}

Drop the columns that you think you can live without:

ALTER TABLE wp_posts DROP COLUMN comment_status;
ALTER TABLE wp_posts DROP COLUMN ping_status;
ALTER TABLE wp_posts DROP COLUMN to_ping;
ALTER TABLE wp_posts DROP COLUMN pinged;
ALTER TABLE wp_posts DROP COLUMN comment_count;

See what breaks over the next few days. If WordPress is unhappy about something, it is not shy about telling the world that something is wrong.

Then, if nothing bad happens...
Begin modifying the WordPress API to handle those fields differently (if at all) and tweak the database to be more performant.

Working on a large project like WordPress can bring a great deal of satisfaction, particularly if any of your modifications are adopted by the core project.

ASPECT #1

For starters, think about the MyISAM storage engine. It only caches index pages in the MyISAM key cache (sized by key_buffer_size). If any queries against mydb.logtable reads a significant number of index pages from /var/lib/mysql/mydb/logtable.MYI, the query mya purge out index pages from other tables. This would causes other queries for those other table to have to reread them index pages from disk again.

ASPECT #2

Any queries against a large MyISAM table that is infrequently accessed may have old index statistics, possibly resulting in full table scans down the road. During off hours, you should setup a crontab job that runs this SQL command:

ANALYZE TABLE mydb.logtable;

This will rebuild the index statistics.

ASPECT #3

Regardless of Storage Engine (or even RDBMS), any query that requests more that 5% of a table's total size would cause the MySQL Query optimizer to stop using indexes and do full table scans. For example, if you query data for a year and a table contains less than 20 years of log info, it is very probable that a full table scan will happen.

ASPECT #4

If the cardinality of the indexes is very low, a certain key combination could result in a query not using any indexes and do a full table scan.

For a demonstration of low cardinality affecting query performance and EXPLAIN plain generation, see my Nov 13, 2012 post Must an index cover all selected columns for it to be used for ORDER BY?

SUGGESTION

You should schedule some form of log rotation. Perhaps this:

SET @tb1 = 'mydb.logtable';
SET @tb2 = CONCAT(@tb1,'_',date_format(now(),'%Y%m%d_%H%i%s'));
SELECT CONCAT('ALTER TABLE ',@tb1,' RENAME ',@tb2) INTO @sql_1;
SELECT CONCAT('CREATE TABLE ',@tb1,' LIKE ',@tb2) INTO @sql_2;
PREPARE s FROM @sql_1; EXECUTE s; DEALLOCATE PREPARE s;
PREPARE s FROM @sql_2; EXECUTE s; DEALLOCATE PREPARE s;

This will keep the log file lean and mean and archive the most recent entries.

MySQL (MariaDB) – Troubleshooting Strange Performance Issues

Your max_connect_errors is way too low. You have it at 1000. That means after 1000 consecutive connect failures, you cannot connect to MySQL any longer. Your status variable Aborted_connects should be the dead giveaway if it climbs fast.

When you can no longer connect to MySQL even with bunch of open DB Connections, you would have to execute FLUSH HOSTS; and that resets to count to 0.

I would raise max_connect_errors to 1000000000 (1 billion).

I would also consider lowering the following

[mysqld]
interactive_timeout = 30
wait_timeout        = 30