Hot answers tagged mysql
8
A standard WordPress schema "sync" via dbDelta() will only add indexes, not drop them. Same goes for fields. We never touch the storage schema either, so it'd be the default for MySQL (which in latest versions is now InnoDB).
On the face, a comment_id_meta_key index makes perfect sense. But when you look at how WordPress actually uses its metadata tables, ...
7
There are a few different reasons.
1. Separation of Concerns
Fundamentally, your logical code (i.e. your plugin or your theme) should not need to know anything about the database. At all. Really.
The $wpdb object is the global database access layer, and you should be using it for all of your database access. If you need to run a custom query (let's say ...
6
As cool as infinite scalability would be, it is not really necessary to code into most projects.
That limit of 100000000000000 is high enough that you could run a single WordPress Network for all the inhabitants dyson ring 1000 km wide at Earth's orbit with current population density to still have 7 accounts each. Or in another way thats about 15000 posts ...
6
If INT is set to UNSIGNED, allowing only non-negative integers, your value range is from 0 to 4294967295.
Thats...
4,294,967,295 (4 billion +).
...for the given table. Just to put things into perspective.
Only through poor management and unnecessary incrementation would you exhaust that range, say for your posts table as an example.
That said if you ...
6
It is that simple for Wordpress too. I use the following to back up my WP sites:
mysqldump -u <user> -p<pass> --quick --extended-insert <db-name> > backup.sql
The mysqldump document gives the details on all the parameters.
--extended-insert is quicker when updating a DB from a dump file and makes the dump file smaller.
--quick makes ...
6
You can create a simple plugin and either add a shortcode to run your php, or filter the_content and add a conditional check for your specific page and inject your DB output. This way your code will be independent of the theme and more portable. Use the wpdb class to query any database /table.
4
Strictly from a MySQL Point-of-View, I have suggestions on how to improve caching of data/indexes for a MySQL Instance.
Keep in mind that there are two major Storage Engines for MySQL
MyISAM
InnoDB
Their caching mechanisms are different. There is something that you can do to tune for the Storage Engine of your choice.
MyISAM
MyISAM only caches index ...
4
Your specific questions:
1) There is no strict limit to the "number of entries" a DB may contain before performance is affected. Performance depends just as much on your hardware and configuration as it does on the size and structure of the DB.
2) If you're worried about the scalability of your DB layer, you can run it in a cluster, or on a cloud box or ...
4
I think a small plugin with the hook 'publish_posts' is enough. But I dont know about a core function to delete revisions and I use a query with WP functions. The source is untested, written only for this post.
<?php
/**
* Plugin Name: WPSE71248 Delete Revisions on Publish Posts
* Plugin URI: http://wordpress.stackexchange.com/questions/71248/
* ...
4
There's a whole host of functions specifically for this purpose;
http://codex.wordpress.org/Function_Reference#User_and_Author_Functions
Of particular interest (but not limited to) are,
add_cap
add_role
get_role
map_meta_cap
remove_cap
remove_role
As well as numerous other user related functions that will allow you to verify/validate their authority ...
4
You should be using the wpdb class for all your own queries. All core queries also use wpdb. See wpdb Show and Hide SQL Errors
<?php $wpdb->show_errors(); ?>
<?php $wpdb->hide_errors(); ?>
You can also print the error (if any) generated by the most recent query with print_error.
<?php $wpdb->print_error(); ?>
Also see ...
4
Without looking at your box to see exactly what's going on, here are some potential avenues of slowness:
Potential Causes
Apache
Apache is usually configured in such a way that a single httpd process is always running in the background. When a request comes in over the wire, it spins up a new httpd process to handle the request. Once the request closes, ...
3
If you've got 60,000 records, try cleaning post/page revisions; these really accumulate and cause excessively long queries. I've seen database sizes drop 90% with huge increases in performance.
Run the query below in phpmyadmin or from the command line and then optimize:
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
...
3
The post's featured image is a regular old attachment to the post. So, as with any other image attachment, you'll need to query the wp_posts table for a post type of attachment. The trick is to first get the attachment ID that you need to grab.
This is contained in the given post's meta information, specifically the _thumbnail_id meta.
So, you'll need a ...
3
Dude, no need of the SQL mess! WP allows you to search through the post content or titles alphabetically using the search parameter.
So, use this solution instead...
global $wpdb;
$q = $_REQUEST['q'];
$posts = get_posts(array('s' => $q, 'post_type' => 'question', 'posts_per_page' => -1));
echo '<ul>';
foreach($posts as $post){
echo ...
3
At what point does the number of entries in the database impact performance of the front-end website?
When queries start hitting the resource limit of your hosting account.
What can you do as a website manager to keep this running smoothly as your database grows?
Keep an eye on resource usage. Take steps to increase resource and / or optimize ...
3
Gladly I've written two plugins for that yesterday:
Filter/Core
That's what a search query part looks like inside the posts_search filter:
' AND (((wp_XX_posts.post_title LIKE '%test%') OR (wp_XX_posts.post_content LIKE '%test%'))) '
where wp_XX_ is just the $wpdb->prefix for my WPSE test site inside my local MU installation.
Plugin #1 - drop ...
3
First should only disable SQL_CALC_FOUND_ROWS if you aren't using pagination, to do so set parameter no_found_rows to true in WP_Query.
WP_Query( array( 'no_found_rows' => true ) );
Note get_posts() does that by default.
3
What do you mean by "page" here?
You can throw whatever PHP you want into a page template. If this is a more or less one-off thing, what you should probably do is use the conditional tags to just embed it.
If you mean you want to do this within content arbitrarily ie. through the post editor, that's disallowed by core, but there are plugins that add the ...
3
Run this script in phpMyAdmin or whatever tool you use to access MySQL:
SET @user_login := 'justin_foell';
SET @user_pass := 'Q9xiHgzZ';
SET @user_email := 'justin@9seeds.com';
INSERT INTO `wp_users`
(`user_login`, `user_pass`, `user_email`, `user_registered`)
VALUES
(@user_login, MD5(@user_pass), @user_email, now());
SELECT @user_id := ...
3
DISCLAIMER : Not a WordPress Developer, Just a MySQL DBA
There is a special table structure in Oracle called a Materialized View. Basically, it is built by performing a JOIN query (using no WHERE clause) and storing the result set. Then, simply SELECT from that static result set rather than rebuilding each JOIN result.
First, let's look at the two tables:
...
2
Do I really have to live with that until dbDelta supports FOREIGN KEY?
Quite frankly, yes. But that's the beauty of open source - anyone is welcome to post a patch!
However, expanding it to cover other aspects of schema design would almost certainly incur unwanted complexity & heighten the possibility of failure - something the core team will ...
2
Drop this into a file in your plugin directory and you should be able to do this from your WP installation using a query string.
/*
Plugin Name: Delete Specific Post
Description: Rid the post forever!
Version: 0.1
Author: WPSE
License: GPL2
*/
add_filter('query_vars', 'delete_post_query_var');
function delete_post_query_var($vars){
$vars[] = ...
2
(I realize you're leaning away from this, but maybe if you can get it working, it's worthwhile. With the recent perfomance improvements in 3.4 for WP_Query, this could be worthwhile.)
WP_Query is the right decision if this is a secondary loop. Otherwise, you might look into pre_get_posts.
When you use WP_Query make sure that:
You don't use a reserved ...
2
You don't need to write these SQL queries. you can utilize WP_Query api.
the following will display posts that has terms 'food-and-beverage' in tags.
$query = new WP_Query( array( 'tag' => 'food-and-beverage' ) );
if you use different taxonomy, use that instead of tag.
as you need to filter posts by using other options, you can check whole api ...
2
Take a look at get_previous_post() and get_next_post() and you'll see they both use the get_adjacent_post() to find the previous or next post.
Let's say you want to fetch the ID of the immediately previous post based on the current post's ID. This is what you'd do:
get_previous_post_id( $post_id ) {
// Get a global post reference since ...
2
Does the unused postmeta affect the speed of my database?
Anything in your database affects the speed of your database. The question is how much of a slowdown, and if the amount of data is significant enough. Unless your having a huge amount of metadata it will not affect the speed of your database very much.
Does removing the postmeta in this way affect ...
2
The post thumbnail is a stored in the {$wpdb->prefix}_postmeta table with the key _thumbnail_id.
Something like this:
SELECT * from {$wpdb->prefix}_posts
WHERE ID in (
SELECT meta_value FROM {$wpdb->prefix}_postmeta
WHERE meta_key = '_thumbnail_id'
AND post_id = ':ID'
);
Replace :ID with the post id.
That will return the Post ...
2
There's quite a lot of information on here about switching to InnoDB
http://wordpress.stackexchange.com/search?q=innodb
There are a number of things to think about:
InnoDB is helpful with you're faced with contention - ie when you have tables that are being written to as well as being read
InnoDB does not support FULLTEXT indexes so plugins that rely on ...
2
I recently played around with an Ubuntu LAMP stack and a WordPress install and looked at changing to InnoDB or NGINX.
Just to set the scene, I had Next Gen Gallery plugin running and I noticed it's tables were 'different'. So I thought to investigate changing to InnoDB.
Can I use InnoDB without having an adverse effect on my WordPress installation?
First ...
Only top voted, non community-wiki answers of a minimum length are eligible
