Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 3

Displaying posts with tag: count (reset)

Fast paging in the real world
+0 Vote Up -2Vote Down

This blag was originally posted at http://cafuego.net/2010/05/26/fast-paging-real-world

Some time ago I attended the “Optimisation by Design” course from Open Query¹. In it, Arjen teaches how writing better queries and schemas can make your database access much faster (and more reliable). One such way of optimising things is by adding appropriate query hints or flags. These hints are magic strings that control how a server executes a query or how it returns results.

An example of such a hint is SQL_CALC_FOUND_ROWS. You use it in a select query with a LIMIT clause. It instructs the server to select a limited numbers of rows, but also to calculate the total number of rows that would have been returned without the limit clause in place. That total number of rows is stored in a

  [Read more...]
Unqualified COUNT(*) speed PBXT vs InnoDB
+3 Vote Up -4Vote Down

So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table!

So, it’s kinda known but nevertheless often ignored that this operation on InnoDB is costly in terms of time; what InnoDB has to do to figure out the exact number of rows is scan the primary key and just tally. Of course it’s faster if it doesn’t have to read a lot of the blocks from disk (i.e. smaller dataset or a large enough buffer pool).

I was curious about PBXT’s performance on this, and behold it appears to be quite a bit faster! For a table with 50 million rows, PBXT

  [Read more...]
How To Diagnose And Fix Incorrect Post Comment Counts In WordPress
+1 Vote Up -1Vote Down

Introduction

If your WordPress comment counts got messed up, whether because of a plugin (I'm talking about you, DISQUS) or you messed with your database manually and did something wrong (yup, that's what I just did), fear not – I have a solution for you.

But first, a little background.

Comment Counts In WordPress

Here's how comment counts work in WP:

  • Posts live in a table called wp_posts and each has an ID.
  • Comments reside in a table called wp_comments, each referring to an ID in wp_posts.
  • However, to make queries faster, the comment count is also cached in the wp_posts table, rather than getting calculated on every page load.
  [Read more...]
Showing entries 1 to 3

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.