Ben Reyes

Guidelines on optimising SQL Quires

SQL Workload reduced by

1. Efficient structuring of the data (indexes, clusters, keys
2. Efficient structuring of queries
3. Providing more information to the DBMS query optimiser

• Avoid the use of *, count(*) (the system needs to work out at run time what needs to be substituted for the *)

• Avoid very long table names (they take longer to parse)
• Do use brief table aliases in joins
• Do specify table.columns in joins (avoids the optimiser having to work out which one is intended).
• Be Aware of the assumptions the optimiser in your system is making. Indexes etc. will give it more choice, check with and without processing times.

Summary

• JOIN generally faster than nested subquery
• Indexed columns generally faster than non-indexed columns for queries with a hit rate < 20%
• Unique index faster than non-unique
• != Does not use an index unlike other operators
• Non-compressed indexes faster than compressed indexes

From revision notes on an Advance Database System and Technology module at Queen Mary, University of London.

Jeff Bezos (Amazon) on customer feedback

A lot of people often misunderstand how they should utilise customer feedback. People often cry ‘design by committee’ or the opposite.

Jeff Bazos has a good point on this.

Think Long Term - On feedback and being misunderstood: If we think we are right, then we continue.
If we are criticized for something we think we’re wrong on, we change it, we fix it.

It’s really important to think about this things, but never to buckle to  standard kind of pressure that forces really short term kind of thinking. It’s a competitive advantage to think long term.

- Jeff Bezos 

Source: http://www.youtube.com/watch?v=-hxX_Q5CnaA&t=3m55s (3 mins 5 secs)

When And Where To Use Database Indexes

• Don’t setup indexes on small tables which can be loaded in memory.
  There is a certain cost benefit which won’t be achieved in smaller tables
• Do Index Primary Keys
  The majority of the time
• Do Index Secondary Keys
  The majority of the time. But the Database Administrator may complain about the space allocation if there are many secondary key indexes.
• Do Index Attributes that often is queried in WHERE clauses 
• Avoid indexing attributes that are regularly updated
  This has a performance penalty on updates and deletes when the database has to also update the index.
• Industry Standard to index queries that are returning less than 15-20% of rows in a table. Indexing works best on single point queries that return a single row. Anything more than 20% may be overall more efficient to just query the tables directly.
• Avoid Indexing Long Character Strings
  This takes up memory

From my revision notes for an Advance Database System and Technology module. Thought I should add it to my scrap book as it’s pretty solid guidelines on where and when to use indexes.

Decaying Review Scores

A problem (or not) with product reviews and giving them a score is that they don’t often decay (get worst/lower as time goes on).

As a product, movie, restaurant, e.t.c  that was popular before in the year 2000, may no longer be popular or up to the current standards. I’m not too sure what review websites (Amazon, Yelp..) implement a decaying algorithm on scoring. But it would be interesting to find out if any do or if this would actually provide a positive impact on the results.

This was just a random thought as I’m currently working on a website to help make better informed consumer choices for makers, creators and startups. I’m exploring service/product ranking & relevancy algorithms.

Is the public internet inhibiting exploration?

The public internet is inhibiting exploration… Which of course is utter rubbish, but there is some feared truth in that.

That fear is down to the sticky permanent memory of the internet dampening our freedom of exploration and development, especially with teenagers and young people.

I’ve been doing some on/off ethnographic studies in this area for the 3-4 years, which has fascinated me for a while. This is just not true.

Yes there are some edge cases and there is mostly no immediate reset button if someone is actively out there to hurt and harm others or self destruct (but we still forgive & forget). But for the majority of normal young people, they just delete their YouTube, Facebook, Dailybooth, FormSpring e.t.c. and start again. And it’s pretty common.

I’m an advocate for acceptance and forgiveness of exploration/mistake making vs. punishment of open exploration.

I paraphrase Leo Laporte, Gina Trapani and Jeff Jarvis in This Week In Google:

We have to adapt society to a culture where we all have skeletons in the closet recognise that every human does and become more tolerant of such things. If society can’t forgive a kid for doing something dumb, we have a big problem
source

Sharing our mistakes and our process of exploration is a good thing. No one is perfect, maybe if we accept this we can all move along and progress further. (Related talk by Derek Sivers about the creative process)

Institutions, corporations and schools involved in the practice of punishing and filtering based on trivial matters on people’s online trails and history are the ones actively damaging the progress of society and acceptance. 

A feature idea

Which bring me on to a feature that could be embedded into publishing/sharing platforms. Create a blogging platform or plugin to YouTube where content decays automatically moving video blogs or posts to an archive that is not publicly available (unless you add a friend or someone favorites an individual piece).

But it is archived so if you want in a few years or so, you’d still like to share your explorations then you can do so. This way there is reduced level of fear of creating and sharing which should in turn increase public explorative expression.

If no one builds this, I may do so in the future.

Written by Ben Reyes Posted to HackerNews