Programming Blog

Design your database with caution

This first tip may seems obvious, but the fact is that most database problems come from badly-designed table structure.
For example, I have seen people storing information such as client info and payment info in the same database column. For both the database system and developers who will have to work on it, this is not a good thing.
When creating a database, always put information on various tables, use clear naming standards and make use of primary keys.
Source: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

Know what you should optimize

If you want to optimize a specific query, it is extremely useful to be able to get an in-depth look at the result of a query. Using the EXPLAIN statement, you will get lots of useful info on the result produced by a specific query, as shown in the example below:

EXPLAIN SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

Source: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

The fastest query… Is the one you don’t send

Each time you’re sending a query to the database, you’re using a bit of your server resources. This is why, on high traffic sites, the best thing you can do in order to speed up your database is to cache queries.

There’s lots of solutions to implement a query cache on your server. Here are a few:

• AdoDB: AdoDB is a database abstraction library for PHP. It allows you to use the database system of your choice (MySQL, PostGreSQL, Interbase, and way much more) and it is designed for speed. AdoDB provides a simple, yet powerful caching system. And last but not least, AdoDB is licenced under the BSD, which means that you can use freely on your projects. A LGPL licence is also available for commercial projects.
• Memcached: Memcached is a distributed memory caching system which is often used to speed up dynamic database-driven websites by alleviating database load.
• CSQL Cache: CSQL Cache is an open-source data caching infrastructure. Never tested it personally, but it seems to be a great tool.

Don’t select what you don’t need

A very common way to get the desired data is to use the * symbol, which will get all fields from the desired table:

SELECT * FROM wp_posts;

Instead, you should definitely select only the desired fields as shown in the example below. On a very small site with, let’s say, one visitor per minute, that wouldn’t make a difference. But on a site such as Cats Who Code, it saves a lot of work for the database.

SELECT title, excerpt, author FROM wp_posts;

Use LIMIT

It’s very common that you need to get only a specific number of records from your database. For example, a blog which is showing ten entries per page. In that case, you should definitely use the LIMIT parameter, which only selects the desired number of records.
Without LIMIT, if your table has 100,000 different records, you’ll extract them all, which is unnecessary work for your server.

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

Avoid queries in loops

When using SQL along with a programming language such as PHP, it can be tempting to use SQL queries inside a loop. But doing so is like hammering your database with queries.
This example illustrates the whole “queries in loops” problem:

foreach ($display_order as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

Here is what you should do instead:

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

Source: http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

Use join instead of subqueries

As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:

SELECT a.id,
    (SELECT MAX(created)
    FROM posts
    WHERE author_id = a.id)
AS latest_post FROM authors a

Although subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.

SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p
    ON (a.id = p.author_id)
GROUP BY a.id

Source: http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

Be careful when using wildcards

Wildcards are very useful because they can substitute for one or more characters when searching for data in a database. I’m not saying that you shouldn’t use them, but instead, you should use them with caution and not use the full wildcard when the prefix or postfix wildcard can do the same job.
In fact, doing a full wildcard search on a million records will certainly kill your database.

#Full wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  'hello%';
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  '%hello';

Source: http://hungred.com/useful-information/ways-optimize-sql-queries/

Use UNION instead of OR

The following example use the OR statement to get the result:

SELECT * FROM a, b WHERE a.p = b.q or a.x = b.y;

The UNION statement allows you to combine the result sets of 2 or more select queries. The following example will return the same result that the above query gets, but it will be faster:

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y

Source: http://www.bcarter.com/optimsql.htm

Use indexes

Database indexes are similar to those you can find in libraries: They allow the database to find the requested information faster, just like a library index will allow a reader to find what they’re looking for without loosing time.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order.

The following query will create an index on the Model column from the Product table. The index is called idxModel:

CREATE INDEX idxModel ON Product (Model);

Source: http://www.sql-tutorial.com/sql-indexes-sql-tutorial/

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

10 sql tips to speed up your database

Go to Source

Getting started with CouchDB

Apache CouchDB is one of a new breed of database management systems. These new systems are known as NoSQL. NoSQL is a buzz word term first popularized in early 2009 to describe a database that is non-SQL… NoSQL is a term for a loosely defined class of non-relational data stores that break with a long history of relational databases and ACID guarantees. Data stores that fall under this term may not require fixed table schemas.

The first reason I am quickly growing to love CouchDB, and hence decided to write this post is due to the fact that it is a document-oriented DB, rather then storing content into set tables, it allows us to store information, in a manor that is as flexible as an array.

For example here’s a sample document:

FirstName="Bob", Address="5 Oak St.", Hobby="sailing".

However another document could have this data:

FirstName="Jonathan", Address="15 Wanamassa Point Road", Children=("Michael,10", "Jennifer,8", "Samantha,5", "Elena,2").

This is great because first of all, we are not wasting storage on empty, or null fields.

The second reason this is nice, is that we no longer worry about tables, and columns! we need to set info, then we set just what we need. This CAN cause issues if you do not plan correctly, but we will get into that a little later on.

Another big reason I like CouchDB, is that access is through a REST API, for those who know what that means, this is big! For those who don’t, it means access to get or set data can be granted directly from the browser via javascript, without the need to write extra PHP code on the server side!

Using CouchDB

Now that I have you all hyped about it, lets get to using it. The first thing you need to know is that PHP does not have any built in functions to access a CouchDB database.
To do this I recommend PHPillow, a class written by Kore Nordmann. It is definitely one of the best I have seen so far. The second thing you need to know is that setting, and querying a CouchDB is not the same as a MySQL query. As I stated, PHPillow is the best (in my opinion) way to access CouchDB, so that is what I will be using in this example…

Database connection
To connect to your CouchDB instance simply use the phpillowConnection class like shown here:

phpillowConnection->createInstance('localhost', 5984, 'user', 'password');

Once created this connection will be used in your document and view classes automatically.

Define a custom document
All documents extend the abstract base class phpillowDocument. A complete model defining a blog entry could look like:

class myBlogDocument extends phpillowDocument
{
    protected static $type = 'blog_entry';

    protected $requiredProperties = array(
        'title',
        'text',
    );

    public function __construct()
    {
        $this->properties = array(
            'title'     => new phpillowStringValidator(),
            'text'      => new phpillowTextValidator(),
            'comments'  => new phpillowDocumentArrayValidator(
                'myBlogComments'
            ),
        );

        parent::__construct();
    }

    protected function generateId()
    {
        return $this->stringToId( $this->storage->title );
    }

    protected function getType()
    {
        return self::$type;
    }
}

The static property $type defines the type of the stored document and should be unique for each document in your application. If you are implementing a module, prefix this type with the name of the module, like “blog” in this example. If you happen to use a PHP version prior 5.3 you have to return the document type in each of your document classes like shown above. 5.3 and above users can use a more generic approach with returning static::$type in a base document class.

The $requiredProperties array defined the properties, which are mandatory to be set. The properties itself are defined in the $properties property, which is initialized in the constructor of the document. We associate a validator with each property which validates the input set on the document. There are quite complex validators, like the phpillowDocumentArrayValidator shown here, which will be described later, which are all documented in the generated API documentation.

The last thing you need to define is the generation of the document ID. An ID in CouchDB needs to fulfill some requirements, which are ensured by using the protected method stringToId(). Normally you use one somehow unique property of the document. If this is not entirely unique the document handler will append something, so that it will get unique. Just return null if you want CouchDB to give you an unique id for the document.

Using a document
Now to save data using the document layout that the above code would create we can simply call:

$doc = new myBlogDocument();
$doc->title = 'New blog post';
$doc->text  = 'Hello world.';
$doc->save();

With the call to the save() method the document will be generated and stored in the database. After this a new magic property is available for the document:

$doc->_id;

Using documents directly this ID is the way to fetch the document back from the database, like:

$doc = new myBlogDocument();
$doc->fetchById('blog_entry-new_blog_post');

This call retrieved the above document back from the database. The magic CouchDB properties _id and _rev (for revision) are set for the document. Beside the defined properties another property has been created by the wrapper, called revisions, which contains all old (and the current) revisions of the document:

echo $doc->revisions[0]['title'];

If you now change a property on the object and store it again in the database the old revision will also be stored in the database, so that no information is lost on change. This behavior may be deactivated by setting the $versioned property to false.

Did you say revisions?
Why yes I did! Thanks for noticing! My Steve Jobs “One more thing!” moment, is that if you alter a document in a CouchDB database, it save the pervious version as a revision automagicly! No need for multiple database entries to make sure your application can roll back!

So thats about it for this tutorial. Next time we will get into how to run more advanced queries using PHPillow.

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

Getting started with CouchDB: a beginner’s guide

Go to Source