14 October, 2008 1:44 pm

MySQL Query Performance Tips

Posted by Richard Wong under Database, Development | 2 Comments

Almost all of the modern web applications involve the use of relational database. So the performance of your applications are very much affected by the quality of the SQL queries you run. Here are some simple performance tips from MYSQL forge that you should consider when writing queries.

  1. Use EXPLAIN to profile the query execution plan
  2. Use Slow Query Log (always have it on!)
  3. Don’t use DISTINCT when you have or could use GROUP BY
  4. Insert performance
    1. Batch INSERT and REPLACE
    2. Use LOAD DATA instead of INSERT
  5. LIMIT m,n may not be as fast as it sounds
  6. Don’t use ORDER BY RAND() if you have > ~2K records
  7. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
  8. Avoid wildcards at the start of LIKE queries
  9. Avoid correlated subqueries and in select and where clause (try to avoid in)
  10. ORDER BY and LIMIT work best with equalities and covered indexes
  11. Separate text/blobs from metadata, don’t put text/blobs in results if you don’t need them
  12. Delete small amounts at a time if you can
  13. Make similar queries consistent so cache is used
  14. Don’t use deprecated features
  15. Turning OR on multiple index fields (<5.0) into UNION may speed things up (with LIMIT), after 5.0 the index_merge should pick stuff up.
  16. Use INSERT … ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT

Now if you know all the above tips, then may be you want to start looking at database scaling, design and more from MYSQL forge.

Here are some more detailed articles about optimizing MySQL for those of you want to get your hands dirty:

5 October, 2008 5:17 pm

Creating User Flows with Product Planner

Posted by Richard Wong under Development, Tools, Web Apps | 0 Comment

When you develop any sort of websites, you need to work out the flow / journey of your users. It is such an important part of any web product. If you got it wrong, you could be left with confused and unhappy user that would eventually lead to the lost of users.

Recently I found this little web tool called Product Planner that allow you to easily create user flows and share it with others.The idea is that by looking at examples of other successful web products, you can get a better idea of how to create your own.

Continue reading »

2 October, 2008 11:31 am

5 Online Tools to Test the Loading Time of your site

Posted by Richard Wong under Development, Tools, Web Apps | 1 Comment

As a blogger / developer, it is always fascinating to know the loading time of your website and compare it with others. Of course, the loading time of your website depends on many different factors. It could be anything between the software and hardware that power your site. But users of your site does not really care about any of this. It is how long it takes for them to see your site on their browser that counts.

So here I prepared a list of free websites that can help you to test the loading time of any website.

1. Pingdom Tool

Probably the best tool from this list. It loads a complete HTML page including all objects (images, CSS, JavaScripts, RSS, Flash and frames/iframes). It looks very similar to the Firebug “Net” feature. The load time of all objects is shown visually with time bars.

Continue reading »

8 September, 2008 11:54 am

10 Laws of Simplicity Developers should know

Posted by Richard Wong under Development | 3 Comments

Recently I revisited “The Law of Simplicity” by John Maeda. He is a graphic designer, visual artist, and computer scientist at the MIT Media Lab. In the book, John proposes 10 laws for simplifying complex systems in business and life.

Although the book are mostly focused on product design, it could be applied to anything especially software development. It doesn’t matter whether you are coding or designing simplicity will always work and sale. Simpler code means less bug and less maintenance cost. Simpler interface means easier to learn and use.

Continue reading »

28 August, 2008 12:16 am

Evaluate the Quality of Your Website

Posted by Richard Wong under Development, Others | 0 Comment

Since I just updated the design of the site, I start thinking whether this new design is better. But realising there so many different aspect of site that we need to look at in order to measure the quality.

Then I found 50 Questions to Evaluate the Quality of Your Website by Carsten Cumbrowski from my starred item in google reader. It covers a wide range of areas including accessibility, design, navigation and more.

A sample of questions includes:

  • Is content structurally separate from navigational elements?
  • Are links labeled with anchor text that provides a clear indication of where they lead?
  • Do clickable items stylistically indicate that they are clickable?
  • How intuitive is it to navigate? Are signs obvious or obscured? Buttons/Links Like Text, that are not clickable and vice versa, links/buttons that cannot be identified as such
  • Clear statement of PURPOSE of the site? Purpose must become clear within a few seconds without reading much or no text copy at all.
  • Are the colors used harmonious and logically related?
  • The fonts should be easily readable, and degrade gracefully.- Should look OK on various screen resolutions.
  • Does the copywriting style suit the website’s purpose and ’speak’ to its target audience?

Although the list isn’t particularly targeting developers / designer, I think it is still a very useful list to reference if you are running your own site. If you can answer YES on most of the questions, you have done a great job. For me there are still a lot of work to do here…

Previous Posts