5 votes
Accepted

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

(This answer is probably more than you wanted. It is an in-depth discussion of the source of the problem, plus multiple solutions.) Long ago, when MySQL added CHARACTER SET utf8, it needed up to 3 ...
Rick James's user avatar
  • 77.6k
3 votes
Accepted

Select the maximum aggregate value for a limited set of chunked/grouped rows in a large database?

There seems to be a bug in MySQL (checked up to 5.6.21 on sqlfiddle.com) where the optimizer does not identify the optimal index when it is available. It can be worked around by using force index() ...
jkavalik's user avatar
  • 5,080
3 votes
Accepted

Migrate SQL Server backup to MySQL for a Wordpress site

Good luck. You'll need to do way more work than just a "restore". Essentially, you'll need to create a custom pipe to convert the data in the SQL Server database into the table structure used by ...
Hannah Vernon's user avatar
  • 70k
3 votes
Accepted

Is it normal that our Wordpress' database crashed multiple times in this month alone?

Don't ever use MyISAM. Ever. Under any circumstances. Just don't. If you need its performance characteristics for some reason, use MariaDB with Aria tables. MyISAM is not crash safe, and will corrupt ...
Moscato's user avatar
  • 64
3 votes
Accepted

WordPress with massive MySQL database slow. Would MySQL replication help?

WordPress WordPress was not designed with scaling in mind. (Caveat: What I say here is based on looking at innards several years ago; things may have changed.) There is no "read-write" separation, ...
Rick James's user avatar
  • 77.6k
3 votes

Advice for database implementation

... save information about the children and several pictures and documents ... ... A friend is currently saving the data of over 70 kids in folders and subfolders on her device ... ... We need the ...
Phill  W.'s user avatar
  • 8,568
2 votes

Join two SELECT results with the same post_id value

Are you looking for IN? SELECT wp_postmeta.post_id FROM wp_postmeta WHERE wp_postmeta.meta_key in ('_downloadable', '_virtual') AND wp_postmeta.meta_value='no'; Updated answer as per updated ...
mysql_user's user avatar
  • 1,932
2 votes
Accepted

Dedicated tables VS Multipurpose Tables

When the data pattern is the same and the data is possibly related, such a setup is completely fine and even desirable as it makes certain tasks (such as supporting a search function) a lot easier. ...
Duffy's user avatar
  • 1,054
2 votes
Accepted

In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure)

My translation of your problem is (slightly altered from your description): For each post_id, find the value from meta_value column where meta_key = '_max_variation_regular_price' and put it in ...
ypercubeᵀᴹ's user avatar
2 votes

optimize wordpress mysql queries for millions of rows

Query 1: My condolences, your finger must have fallen off from clicking the Next button to get to OFFSET 5261200 !! OK, so maybe some search crawler followed that button. (Google has sturdy fingers....
Rick James's user avatar
  • 77.6k
2 votes
Accepted

MySQL 5.5 vs 5.6,5.7 above same database same query but different output, limit have duplicated ID

Added ORDER BY up_posts.post_date DESC, up_posts.ID DESC no more duplicate result. Seem like we need to manually add this to avoid an unnecessary problem. FYI. It applied to MySQL 5.6 and 5.7. See ...
Shiro's user avatar
  • 153
2 votes

Help me understand and resolve MySQL deadlock - "lock mode AUTO-INC waiting"

The schema for n0_table looks like it is straight from Wordpress? It has multiple inefficiencies. Revamp it to make queries faster and either eliminate or decrease the frequency of the original ...
Rick James's user avatar
  • 77.6k
2 votes
Accepted

How to avoid DROP TABLE for a specific MySQL table

prevent DROP TABLE from being executed ONLY FOR THIS TABLE But dropping some another table in the same database must be available? If so the task cannot be solved using MySQL privilege system. ...
Akina's user avatar
  • 19.7k
2 votes

Error Referencing wp_users as Foreign Key

The MyISAM engine doesn't support foreign keys. Use the InnoDB engine instead. Other observations: The referenced table has no index on display_name. As per documentation, it is required to have an ...
Jehad Keriaki's user avatar
2 votes

Unexpected very slow user count in WordPress

Add this composite index to wpe_posts: INDEX(post_status, post_type, post_author). The answer to "why": In 5.5 (and before), IN ( SELECT ... ) was a terribly slow construct. Amd see if ...
Rick James's user avatar
  • 77.6k
1 vote

How to fix MySQL high CPU usage running on VPS

key_buffer_size implies MyISAM. Don't use for MyISAM unless you have an overwhelmingly good reason (and it is a near certainty that you don't in 2020). query_cache almost always does more harm than ...
Gordan Bobić's user avatar
1 vote
Accepted

mysql > why replace command is not working on strings coming from json encoded?

As a primer it looks like the data is being stored with the escape character \ backslash to allow the WordPress server to automatically use the data in the engine itself. You would have to double ...
John K. N.'s user avatar
  • 17.6k
1 vote

MySQL: Can not add foreign key into create table statement

I am not sure about word press, but if you are using MySQL then you need to change your foreign key structure Original: foreign key (fk_coupons_targets) references wp_frequentVisitorCoupons_coupons(...
Ahmad Abuhasna's user avatar
1 vote
Accepted

Mariadb times out on startup after system reboot

These messages: [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [ERROR] mysql.db: 1 client is using or hasn't closed the table properly are a symptom of a crash and ...
Rick James's user avatar
  • 77.6k
1 vote

How to use Unicode chars on PHPMyAdmin?

Your UPDATE statement looks correct. Here's "proof": mysql> SELECT HEX(REPLACE( " !", ' !',' !')); +----------------------------------+ | HEX(REPLACE( " !", ' !',' !')) | +----------------------...
Rick James's user avatar
  • 77.6k
1 vote

Error establishing a database connection

"Cannot allocate memory for buffer pool" says that you have too much going on in too little RAM. Increasing swap will get you going, but adding more RAM will speed things up considerably (over ...
Rick James's user avatar
  • 77.6k
1 vote
Accepted

Error establishing a database connection

Your swap space indicated by free -m looks undersized to me, do not be bashful, make it large enough to survive when swap is needed for anything. With only 4G RAM, if you have space available, 16G ...
Wilson Hauck's user avatar
  • 1,728
1 vote

Temporary tables created on disk: is huge

You "can't tune your way out of a performance problem". So let's look for a villain and fix it. Change long_query_time down to 1. Wait a day. Then run pt-query-digest. More: http://mysql.rjweb....
Rick James's user avatar
  • 77.6k
1 vote
Accepted

How to increase performance with joins to create VIEW from Wordpress wp_usermeta

In this case you don't need to join the table to itself. If you only query it once then it will go faster. SELECT user_id, MAX(CASE WHEN meta_key='vorname' THEN meta_value ELSE null END) ...
indiri's user avatar
  • 2,211
1 vote

WordPress database error Table './databasename/wep_options' is marked as crashed and should be repaired for query

I think you are using MyISAM Engine Don't use MyISAM for any case. It's becoming deprecated. To get rid of this problem first repair your table. Do the following: REPAIR table wep_options; ...
HamoonDBA's user avatar
  • 617
1 vote

Error Referencing wp_users as Foreign Key

For a foreign key field to work it must have the same exact data type as the field it's referencing - this includes attributes. I was just pulling my hair out over the exact same thing but it worked ...
James Cushing's user avatar
1 vote
Accepted

PostgreSQL to MySQL migration to use database for WordPress site - how to's?

Any specific reason to migrate to MySQL? https://wordpress.org/plugins/postgresql-for-wordpress/ Else MySQL Workbench will work. You'll always have to take into consideration any functions/triggers, ...
amacvar's user avatar
  • 689
1 vote

MySQL hundreds of sleep processes giving "Error establishing a database connection" error

(Partial answer only for the queries shown, you maybe identify other offenders when this is fixed.) SELECT COUNT(*) FROM wp_postmeta WHERE meta_key = '_loved' AND post_id = 669 For this query the ...
jkavalik's user avatar
  • 5,080
1 vote

Select the maximum aggregate value for a limited set of chunked/grouped rows in a large database?

(post_type, post_modified_gmt, post_status) is 'optimal' because it is (1) covering, and (2) ordered in perhaps the best way, and (3) the table is MyISAM, yet this index is much smaller. MyISAM is a ...
Rick James's user avatar
  • 77.6k
1 vote
Accepted

Performance penalty for using thousands of tables in MySQL

Some day WP will realize that their design of 1 database per user has a scaling problem. 1 user versus 100 users is probably not much difference. 1K-10K begins to show pain, depending on how many ...
Rick James's user avatar
  • 77.6k

Only top scored, non community-wiki answers of a minimum length are eligible