MySQL is an open-source, relational database management system.
14
votes
6answers
10k views
Using wpdb to connect to a separate database
I want to connect wpdb to another database. How do I create the instance and pass it the database name/username/password?
Thanks
13
votes
7answers
8k views
Why is my database import losing text widget data?
I've created a site in WordPress on our development machine. In the theme we're using there are numerous widget zones to display text in (sidebar and front page). I've used simple Text widgets in all ...
9
votes
2answers
3k views
Wordpress Database Slow - should I switch to InnoDB?
I have a WordPress site with well over 10k posts, and things are starting to get very slow whenever I am adding and editing posts. Pages load nice and fast for users, along with the admin lists of ...
9
votes
2answers
3k views
MySQL Database User: Which Privileges are needed?
The short installation instruction for WordPress ("5 Minutes") state that:
Create a database for WordPress on your web server, as well as a MySQL user who has all privileges for accessing and ...
7
votes
3answers
324 views
What's a good strategy for planning Wordpress DB growth?
Hoping for some feedback on the topic of optimizing and staying on top of performance as a Wordpress database grows... to paint a picture.... let's say you have a wordpress / buddypress MU site that ...
7
votes
2answers
3k views
The MySQL alternatives: Do Percona Server and MariaDB work well with WordPress, and do they make WordPress go better?
Lately I've been reading about MariaDB and Percona Server, two strong alternatives to MySQL that have gained popularity since Oracle bought Sun.
As far as I know, both of them could be used with ...
7
votes
1answer
148 views
How do I optimize a custom post type admin page with 25,000 posts?
I'm getting the occasional excessive resource limit error to due to loading so many posts at once. We had set the PHP limit to 128 but just loading the list of posts for a couple of the post types is ...
6
votes
1answer
458 views
Would switching to InnoDB from MyISAM improve performance of comments table?
A site I've built gets lots of comments in a short space of time - so perhaps 100 comments might be left in 5 or 10 minutes.
This seems to cause a pretty heavy load on the database, especially when ...
6
votes
1answer
190 views
dbDelta support for FOREIGN KEY
On PHP 5.3.13/MySQL 5.5.21 the following code doesn't work:
if($check_custom_fields_form!=1){
$sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (
`form_name` ...
5
votes
8answers
4k views
How to replace the domain name in a Wordpress database?
I have a Wordpress database which was installed in a development environment... thus, all references to the site itself have a fixed IP address (say 192.168.16.2). Now, I have to migrate that database ...
5
votes
6answers
16k views
What SQL Query to do a simple find and replace
Whenever I create a new website I first create a staging site on a subdomain like "stage.domain-name.com".
After everything works correctly I export the database, open it in notepad++ and do a ...
5
votes
2answers
187 views
WordPress database scalability from the code perspective
I have read through multiple questions here on StackExchange that concern WordPress scaling and the common opinion is that WordPress is indeed highly scalable provided you have the infrastructure to ...
5
votes
2answers
159 views
Lack of composite indexes for meta tables
Why wp_commentmeta table does not have a composite index (comment_id, meta_key) by default? I believe it's the only useful index for that table, am I wrong?
Instead, it has a strange set of indexes:
...
5
votes
3answers
302 views
is it possible to run some php code within a wordpress page?
I'd like to access a DB table and render it in some data grid format using php on a dedicated wordpress page, accessible to members of a certain group. Is this at all possible with wordpress or is ...
5
votes
1answer
292 views
How does Wordpress handle MySQL row lock errors?
If there is a WordPress plugin which updates rows in a custom table, but that update encounters a row lock, what happens? E.g. two users simultaneously fire an update on TABLE1, one setting AGE=1 and ...
5
votes
3answers
339 views
Resetting comment count
Recently, I had to move to another domain and had to recreate all my MySQL tables on the enw server. I noticed that for some posts, the number of comments is doubled. Example - ...
5
votes
2answers
383 views
Complex meta query with 3 keys
I think the problem essentially relates to sql query structure and I am not an expert....
I need to search for posts (custom post type) by 2 parameters:
pd_city
pd_country
Please note that ...
5
votes
3answers
551 views
MySQL Replication Latency Issues in wp-admin pages
I have an environment running WP 3.0.1 with a Master db and two slaves. I am using HyperDB to force all writes to go to the Master, and all reads to read from the two slaves.
I am experiencing a ...
4
votes
3answers
2k views
Optimize Multiple Taxonomy Term MySQL Query?
(Moderator's note: Title was originally: "query/database optimisation")
I'm written a function for a custom "filter" search panel that allows users to select terms from up to four custom taxonomies. ...
4
votes
2answers
184 views
Is it possible to define two databases for one installation?
Is it possible to define 2 database for one wp installation, I have searched but there are options for one database and 2 installations. I want this because, one of my clients site is hosted on ...
4
votes
2answers
1k views
Get posts by menu ID
I'm a little stuck here. I have a menu in Wordpress with some posts in it.
What I would like is to retrieve all the posts as objects.
I think I'm pretty close, but the SQL query doesn't seem to work ...
4
votes
2answers
2k views
Checking if Database Table exists
I read the wordpress codex and professional wordpress. It seems both use something like
if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
to determine if the table exists. Is ...
4
votes
2answers
59 views
How to log mysql errors from wordpress core?
I am developing a wordpress site and I would like to be able to log and handle mysql errors from wordpress core.
My site has a mix of wordpress pages and posts + a few php pages that run under the ...
4
votes
1answer
606 views
Set page template for all pages?
I know the page template is set in the wpdb as meta_key = _wp_page_template and meta_value = template.php.
How can I write a SQL script to set each post where post_type = 'page' to a specific ...
4
votes
1answer
97 views
Custom query to get post names beginning with a digit
I'm implementing an A-Z index for a custom post type. I've got my links printing out the page and the query working for the letters. However, the numbers are tripping me up because I don't want them ...
4
votes
2answers
2k views
WooCommerce with thousands of products - site is very slow - optimize db queries?
So far I've got around 8,000 products in my WooCommerce and the product page on the front end takes several seconds to load, even after caching.
My VPS host says that I should have multiple ...
4
votes
1answer
175 views
find a random blogid across my multisite network that has at least one post published
I need to find a random blog across my multisite network that has at least one post published of a certain post_type, and return its blogid as a variable so I can use it in a switch_to_blog() call.
I ...
4
votes
1answer
530 views
Searching users with WP_List_Table plug-in
I am trying to devlope a plugin that will search and return my users so I can edit some other metatdata that belongs to them. I have been using the plug-in listed on the Codex as my starting point.
I ...
4
votes
1answer
201 views
Built to scale, or worry later?
I'm starting a service that offers websites that runs on a giant Wordpress multi-site. I have a cloud based vps with nginx-php5-php-fpm-mysql. Storage with Amazon S3, distribution with MaxCDN. WP ...
3
votes
4answers
735 views
the_date() not working
I am using wordpress 3.2 and I did a query post like this:
<?php query_posts("posts_per_page=1post=type&page=post_parent=10");?>
Then I try to echo out the date of this post I queried ...
3
votes
2answers
458 views
Can i use php sql functions instead of $wpdb?
New to wordpress and was wondering why in Wordpress API do they tell you to use functions such as prepare(),insert(),get_col(),get_row(), query(), etc. when technically i can use the built in PHP sql ...
3
votes
2answers
362 views
How To Export/Import WordPress (MySQL) Database Properly Via Command-Line?
Normally, a MySQL database can be exported and imported using these simple SSH commands:
Export:
mysqldump -u USERNAME -p PASSWORD DATABASE_NAME > filename.sql
Import:
mysql -u USERNAME -p ...
3
votes
7answers
1k views
WordPress MySQL & PHP Performance
have a website (www.americanbankingnews.com) that gets 40,000-50,000 page views today. It's currently sitting on a dedicated quad-core Xeon server with 8GB of ram. The site is powered by WordPress and ...
3
votes
2answers
1k views
How can I find the source of slow queries in Wordpress?
I have a WordPress site with more than 8000 posts and everytime I add a new one the site becomes unresponsive. I checked the MySQL slow queries log and found out that it is performing a select that ...
3
votes
1answer
487 views
Using source control with WordPress
I just recovered from a pretty bad crash using Server 2008 Shadow copy. Ultimatly I didn't lose more than a few hours of work but a loss is a loss.
I've decided to move to version control to prevent ...
3
votes
1answer
262 views
Extending search query with additional $sentence value
Next in my quest to extend WordPress is altering how WordPress uses the the $sentence variable in posts_search to include an additional string, when the $sentence variable meets a certain criteria.
...
3
votes
2answers
83 views
Wordpress will not operate correctly
My website is ImASpy.com. If you click on the link, Findings, this takes you to my first Wordpress page. It appears to work fine until you click on comments, or you try to go to the about page, which ...
3
votes
1answer
651 views
importing data from non-wordpress mysql db
I have created an import file to get the articles from a non-wordpress db and bring them in as posts in wordpress, I am not sure how to bring over the thumbnail image into my custom field I defined as ...
3
votes
1answer
377 views
Return ID of authors who have at least one post
I found a great piece of code here that returns the ID's of users by role. What I would like to do is modify it so it only returns the ID's of the users who have at least one post. I have tried to ...
3
votes
1answer
278 views
What specific database privileges does WordPress need?
I want to create a new database user for my WordPress installation. Usually on localhost, i can just use root, but it is good practice to use a user with as little rights as possible.
What are those ...
3
votes
1answer
250 views
WordPress use innodb by default?
I just created a new WordPress site in my server. Normally, WordPress uses MyISAM as the database type. But this time, it created all InnoDB tables! Is this normal? Has WordPress switched to InnoDB in ...
2
votes
3answers
3k views
How do you properly prepare a %LIKE% SQL statement?
I'd like to use a LIKE %text% statement while still using the WordPress $wpdb class to sanitize and prepare input.
SELECT column_1 from `prefix_my_table` WHERE column_2 LIKE '%something%';
I've ...
2
votes
3answers
249 views
Programmatically changing role information; editing role name and deleting
I am making system where I am delegating a lot of functionality to the front end of a system using Wordpress. I have listed all the roles (most of the custom) and need to ability for a logged in user ...
2
votes
3answers
777 views
Retrieve featured image
We are building an Android app based on a Wordpress mysql database. We were able to get most of the information from the datase to display on to the app. Now all we have left is to retrieve the posts ...
2
votes
1answer
838 views
How to Delete Old Comments by Date?
I run a contest/giveaway blog at www.mommylivingthelifeofriley.com and currently have over 160,000 comments on 1100 posts. I would like to find a way to delete all comments from my site which are ...
2
votes
2answers
528 views
If I consider changing my WordPress DB tables to InnoDB, will it have an effect on the way WordPress works?
I found a superb post here about optimising WordPress for large installs.
One of the steps refers to lighthttpd which sounds excellent but isn't currently supported by DirectAdmin. Last time I messed ...
2
votes
2answers
543 views
modify all posts of a category to “no comments allowed”
I'm looking for a way to modify all posts of a certain category to be "no comments allowed" without going into each post settings, one by one, and modifying manually.
Perhaps a MySQL statement?
...
2
votes
1answer
256 views
How to disable `SQL_CALC_FOUND_ROWS` [duplicate]
Possible Duplicate:
wordpress query causing load
In the site I'm trying to optimize, I noticed several queries that are rather slow and start with SELECT SQL_CALC_FOUND_ROWS
Searching this ...
2
votes
2answers
326 views
Database slowdown after update to 3.4.1
I own a wordpress installation, and Updated to 3.4.1.
After the upgrade, I got a very strange slowdown to my wordpress.
After all, I used the
define('SAVEQUERIES', true);
in my wp-config.php with ...
2
votes
2answers
268 views
sql query to put quotes around numbers in img tag
I have quite a few old posts that contain an img tag with height and width with no double quotes around the numbers. For example:
<img height=319 alt="" ...
