Jaslabs: High performance Software

by Justin Silverton

The following are five ways to improve queries involving table inserts:

1) use LOAD DATA INFILE when loading data from a text file

This is around 20 times faster than using insert statements.

2) use INSERT statements with multiple VALUES lists to insert several rows at a time

This is many times faster than using separate single-row insert statements. Tuning the bulk_insert_buffer_size variable can also make inserts (to tables that contain rows) even faster.

3) enable concurrent inserts for myisam tables

The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to 1. If concurrent_inserts is set to 0, concurrent inserts are disabled. If the variable is set to 2, concurrent inserts at the end of the table are allowed even for tables that have deleted rows.

4) use insert delayed

This is useful if you have clients that cannot or need not wait for the insert to complete. This is a common situation when you use MySQL for logging and you also periodically run select and update statements that take a long time to complete. When a client uses insert delayed, the server returns right away, and the row is queued to be inserted when the table is not in use by any other thread. Another benefit of using insert delayed is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

5) lock your tables before inserting (for non-transactional tables)

This benefits performance because the index buffer is flushed to disk only once, after all insert statements have completed. Normally, there would be as many index buffer flushes as there are insert statements. Explicit locking statements are not needed if you can insert all rows with a single insert.

To obtain faster insertions for transactional tables, you should use start transaction and commit instead of lock tables.

by Justin Silverton

The following are 5 changes you can make to your wordpress blog that can improve your search engine visibility.

1. Permalinks - changing your Wordpress permalinks to be search-engine friendly. To change the default link type, go to options->permalinks.

The default link structure option looks like this:

Here is the more seo-friendly option:

2. Create a Sitemap - A Sitemap is an XML file that lists the URLs for a site. It allows webmasters to include additional information about each URL: when it was last updated, how often it changes, and how important it is in relation to other URLs in the site. This allows search engines to crawl the site more intelligently. The sitemap file is supported by many search engines, including google. A free plugin is available here

3. Improve the titles of your articles - Title tags are arguably the most important of the on-page factors for search engine optimization. A free plugin is available here that can help you optimize your titles.

4. Site Submissions - Submit your articles to directories and RSS feed sites. A good site is onlywire.com. Here you can submit your blog articles to multiple social bookmarking sites at a time.

5. Tagging & Meta Keywords - implementing tags on your blog and how to use them as meta keywords. This plugin. Adds you to easily generate Technorati tags at the bottom of your blog entries.

There are a few commercial products out there that allow you to turn your php scripts into an executable. While most of them work well, I have found a way to do it for free, using an open source application. This application is called Wapache (based on the apache web server) and it is open source (distributed under the Apache License 2.0).

WApache doesn’t convert your script directly into an executable, it runs on the combination of a windows app (which uses an embedded IE control) and a stripped down version of apache.

Features

• No Internet Explorer menu, tool bar, or address bar.
• Precise control over placement of windows
• Three types of windows: basic, tool windows, and dialog boxes (modal and modeless)
• Fully customizable drop-down and context menu
• System Tray integration
• Asynchronous data handling
• Works with standard Apache modules like mod_php and mod_perl

screenshot

(this is a screenshot of wapache running phpmyadmin)

How to turn your php script into an executable

1) copy all of your scripts/files into the htdocs directory (make sure that the main file is called index.php)
2) launch bin/wapache.exe
3) you will now see your php script in the application that is running

There is also many options that allow you to configure the app in many different ways. This can be found on line 100 of conf/default.wcf (documentation for this config file can be found here):

<StandardWindow Main>
HorizontalAlign Center
VerticalAlign Middle
Height 60%
Width 60%
3DBorder Off
IconPath "../icons/lightbulb.ico"
</StandardWindow>

This could be used for a demo/trial of a web application. A windows installer could also be used (NSIS works well and is free) to create a fully installable, desktop application.

Since this only relies on the apache web server, it’s also possible to use this with any type of supported scripts.

Download

The latest version of Wapache can be found here

By Justin Silverton

In a recent article I saw today about php/mysql security, called “protecting your php/mysql queries from sql injection”, The following method was described as a safe way to execute mysql queries (preventing what is known as a sql injection attack).

// This is a vulnerable query.
$query = "SELECT * FROM products WHERE name='$productname'";
mysql_query($query);

// This just uses mysql_escape_string
$query = sprintf("SELECT * FROM products WHERE name='%s'",
mysql_real_escape_string($productname));
mysql_query($query);

This will work, with select and insert statements, but will not work with statements such as: LIKE, GRANT, or REVOKE. This is a more secure way of preventing SQL injection attacks.


// This query is more secure
$query = sprintf("SELECT * FROM products WHERE name='%s'",
addcslashes(mysql_real_escape_string($productname),'%_'));
mysql_query($query);


By Justin Silverton

PHP has a built-in command called __HALT_COMPILER__. This command Halts the execution of the compiler. This can be useful to embed data in PHP scripts. Below is an example of a self-extracting php script. When executed, a second php file will be extracted in the same directory called testscript1.php.

<?php

__halt_compiler();H4sICKE9CEcAC3Rlc3RmaWxlMS5waHAAs
7EvyCjg5eLlSk3OyFdQKsnILFYAopLU4hKFtMycVAVDJWteLns7AKhVcUooAAAA

How it works

The file that is extracted is gzipped, base64 encoded, and stored at the end of the the file (right after the __halt_compiler directive). The script uses a custom function called gzdecode to gunzip the file, it is then base64 decoded and written to a file. This is just a simple example to show what is possible with PHP. A more advanced version could use a function to tar and gzip a file so multiple files can be extracted.

The code example from this article can be download here