Category: Wordpress

10 sql tips to speed up your database

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

Top 10 best practices for front-end web developers

Explain which div you’re closing

Most of the time when I’m viewing a website source, I see, at the very bottom of the page, an almost endless list of closing </div> tags. In fact, many beginners think they just have to use divs instead of tables to produce quality code. Divs are cleaners than tables, but without proper code organization, it can be as (or even sometimes more) messy as table based code.

Using indentation is a good start. But a tip that can definitely make you save lot of time is to comment every div tag you’re closing, as shown in the example below:

<div id="header">
  <div id="sub" class="first left">
    ...
  </div><!-- #sub.first.left -->
</div><!-- #header -->

Use a CSS reset

Unless you’re a beginner or if you were on vacation on a desert island for the last 6 years, you might already know how useful a CSS reset it. Because by default, browsers don’t apply the same default styling to HTML elements, a CSS reset will ensure that all element have no particular style so you can define your own without the risk of many cross-browser rendering issues.

html, body, div, span, applet, object, iframe,
h1, h2, h3, h4, h5, h6, p, blockquote, pre,
a, abbr, acronym, address, big, cite, code,
del, dfn, em, font, img, ins, kbd, q, s, samp,
small, strike, strong, sub, sup, tt, var,
b, u, i, center,
dl, dt, dd, ol, ul, li,
fieldset, form, label, legend,
table, caption, tbody, tfoot, thead, tr, th, td {
	margin: 0;
	padding: 0;
	border: 0;
	outline: 0;
	font-size: 100%;
	vertical-align: baseline;
	background: transparent;
}
body {
	line-height: 1;
}
ol, ul {
	list-style: none;
}
blockquote, q {
	quotes: none;
}
blockquote:before, blockquote:after,
q:before, q:after {
	content: '';
	content: none;
}

/* remember to define focus styles! */
:focus {
	outline: 0;
}

/* remember to highlight inserts somehow! */
ins {
	text-decoration: none;
}
del {
	text-decoration: line-through;
}

/* tables still need 'cellspacing="0"' in the markup */
table {
	border-collapse: collapse;
	border-spacing: 0;
}

Source: http://meyerweb.com/eric/tools/css/reset/index.html

Don’t use @import

CSS files can be included using the @import directive. This can be useful when, for example, you want to include a stylesheet into another. Another common practice is to include CSS file in html documents using the following:

<style type="text/css>
  @import url('a.css');
  @import url('b.css');
</style>

While it works, the @import directive is much slower than the other way to include stylesheets into a html document:

<link rel='stylesheet' type='text/css' href='a.css'>
<link rel='stylesheet' type='text/css' href='proxy.css'>

It will not make a difference on low traffic websites, but if you have the chance to own a popular website, don’t waste your visitor’s time using @import.
Source: http://www.stevesouders.com/blog/2009/04/09/dont-use-import/

“Smush” your images

Being a developer, I always found that optimizing my images for the web wasn’t easy. I tried the good old “Save for web” Photoshop command, but most of the time, I ended up with images that were either too big or without a sufficient quality.
As a result, I had the bad habit of using unoptimized images on my websites. This isn’t a problem when you don’t have to care about your site’s bandwidth, but after my recent switch on my vps.net virtual private server, I had to be careful with image sizes.

At this time, I found a very cool tool named Smush It: You enter your unoptimized image url, and Smush It will create a perfectly optimized image for you. You can save up to 70% of the file size, while keeping the original quality. As an example, all the images from my list of online code editors have been “smushed”.

Don’t mix CSS with HTML

As a markup language, the right use of HTML is to organize documents by defining a header, a footer, lists, blockquotes, etc. Some time ago, front-end web developers often used now deprecated HTML attributes to style a particular element.
Nowadays, the style attribute allows developers to insert CSS directly into a html document. This is very useful for testing or when you’re in a hurry. But the style attribute is bad practice, that goes completely against the CSS philosophy.

The following example illustrates how dirty and hard to read a simple line of code can become, with the style attribute:

<a href="http://www.catswhocode.com" style="background:#069;padding:3px;font-weight:bold;color:#fff;">Cats Who Code</a>

Don’t mix Javascript with HTML

Just like mixing your html code with css is bad practice, you shouldn’t use any Javascript in your html documents. The following bad practice illustrates an onclick event:

<a id="cwc" href="http://www.catswhocode.com" onclick="alert('I love this site!');">Cats Who Code</a>

The same result can be achieved using unobstructed Javascript. In this example, I’m using the popular jQuery framework:

$(document).ready(function() {
  $('#cwc').click(function() {
    alert('I love this website');
  });
});

This may seems a bit harder at first, especially for beginners; but it is definitely not, and it will keep your html document clean.

Use conditional comments

You know it, IE sucks, and some clients suck even more by requiring you to create webpages which are compatible with this obsolete browser. To target specific versions of IE, you can use the well known IE hacks, as shown below:

height: 200px; /* normal browsers */
_height: 300px; /* IE6 */
.height: 250px; /* IE7 */
*height: 350px; /* All IEs */

Those hacks are extremely useful sometimes, but they are not the best way to target a specific version of IE, and it will cause your CSS validation to fail.

Instead, you should use the conditional comment shown below to target IE6.

<link href="style.css" rel="stylesheet" type="text/css" />

<!--[if lte IE 6]>
  <link href="ie.css" rel="stylesheet" type="text/css" />
<![endif]-->

Place Javascript file at the bottom

A popular practice of the late 90’s/early 2000’s was to place Javascript files within the <head> and </head> tags. The problem is that your javascript files will be loaded first, and consequently your content will be loaded after.

By placing Javascript files at the bottom of your documents, you’ll ensure that JS files will be loaded only when the content has been properly displayed.

    ...
    <script type='text/javascript' src='jquery.js?ver=1.3.2'></script>
  </body>
</html>

Use HTML semantically

HTML is not a programming language. It is a markup language, used to create structured documents by denoting structural semantics for text such as headings, paragraphs, lists, and more.
If you started to create websites in the good old 90’s or in the beginning of the century, you know how dirty the markup was at the time. But happilly, it has evolved.
Among other things, it is important to use html element semantically. As an example, a navigation menu should always be an unordered list:

<ul>
  <li><a href="#">Home</a></li>
  <li><a href="#">About</a></li>
  <li><a href="#">Contact</a></li>
  <li><a href="#">Blog</a></li>
</ul>

Test WHILE you build to avoid cross-browser issues

One of the biggest mistake I ever made when developing html, CSS, and javascript, was not to test my pages on multiple browser while I was writing them. Instead, I used to write all my code and just view in Firefox to see how it was rendered.
In theory, this should be good. But as you know, cross-browser issues are a major problem for front-end developers, especially due to IE. If you test your documents on Firefox/IE/Chrome while your writing it, cross-browser rendering problems will be much easier to fix. I have lost hours not doing it, so I hope this final tip will help you saving your precious time. To test on multiple versions of IE, I use this very handy tool. Happy coding

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

Top 10 best practices for front-end web developers

How to automatically remove the Nofollow from your posts

Copy the following code, and paste it on the functions.php file from your theme. Once you saved the file file, the rel=”nofollow” attributes will be removed.

function remove_nofollow($string) {
	$string = str_ireplace(' rel="nofollow"', '', $string);
	return $string;
}
add_filter('the_content', 'remove_nofollow');

Thanks to Jeff Starr for this awesome piece of code. Have you checked out the book Jeff wrote with Chris Coyier? It’s called Digging into WordPress and it is great!

Looking for WordPress hosting? Try WP Web Host. Prices starts at $5/month and you can try it for free!