esn studio

I have been reading articles on how to optimise MySQL databases and queries and here are a few tips I have learnt and would like to share with you:

1. Proper use of indexes improve performance
2. Do not perform calculations on an index (eg: if you have an index for a column called salary, do not perform calculation such as salary * 2 > 10000)
3. “LOAD DATA INFILE” is the fastest way to insert data into MySQL database (20 times faster than normal inserts)
4. Use INSERT LOW PRIORITY or INSERT DELAYED if you want to delay inserts from happening until the table is free
5. Use TRUNCATE TABLE rather than DELETE FROM if you are deleting an entire table (DELETE FROM delete row by row, whereas TRUNCATE TABLE deletes all at once)
6. Always use EXPLAIN to examine if your select query is efficient
7. Use OPTIMIZE TABLE to reclaim unused space (Note: Table will be locked during optimisation, so only do it during low traffic time)
8. Better to have 10 quick queries than 1 slow one
9. Use caching to reduce database load
10. Normalize tables to ensure data consistency
11. Use persistent connections
12. Don’t query columns you don’t need, avoid using SELECT * FROM
13. MySQL can search on prefix of indexes (ie: If you have index INDEX (a,b), you don’t need an index on (a))
14. Don’t use HAVING when you can use WHERE
15. Use numeric values (rather than alphabetical values) when performing a join

Other resources:

• Optimizing MySQL: Queries and Indexes
• Optimizing and Tuning Your MySQL Database
• Tip for optimizing large MySQL database queries
• Four Ways to Optimize Your MySQL Database
• MySQL Presentations: Optimizing MySQL

Technorati Tags: optimising, fine tuning, mysql, database, query, optimisiation, sql, databases, php

Question: How do I connect to mysql database server from command line such as PuTTY and ssh?

Answer: The syntax to connect to mysql from command line is as below:

mysql -uUSERNAME -hHOST -pPASSWORD

Replace USERNAME, HOST and PASSWORD with your actual username, host (database server) and password.

You can disable BLINKing text in Firefox easily by following the steps below:

Type about:config in your browser’s address bar

Type blink in the Filter text box and press enter

Double-click on browser.blink_allowed to toggle the value from true to false

Technorati Tags: firefox, ff, settings, browser, blink, tips

This is a step by step tutorial with screenshots on how to create a web 2.0 star burst in Adobe Photoshop. web 2.0 star burst has become very popular recently thanks to web 2.0 hype. Those glossy web 2.0 star bursts are also known as Star flashes, Seal, web 2.0 Badge and can be seen everywhere especially on web 2.0 logos. At the end of this tutorial, you will be able to create a web 2.0 star burst using Adobe Photoshop.

Create a new Photoshop document of 300×300 pixels and name it anything you like.

Change the foreground color to #ffac0b.

(more…)

This is a step by step tutorial with screenshots on how to create a reflection effect in Adobe Photoshop. Reflection effect has become very popular recently thanks to web 2.0 hype. Those shiny reflection effect can be seen everywhere especially on web 2.0 logos. At the end of this tutorial, you will be able to create a web 2.0 logo with a reflection effect using Adobe Photoshop.

Create a new Photoshop document of 500×300 pixels and name it anything you like. I named mine “logo”.

Set the foreground color to #00ccff.

Type any text you want in the document and set the font size to 48px and set the anti-aliasing method to Smooth. As for the font, you can use any font you like. I used VAG Rounded Std because it’s a web 2.0 font.

(more…)