MySQL inline query versus stored procedure comparison

Simple query using group clause for 1 million records resulting in final list of 27 records.

First time takes 0.43053775 secs.
Same query through Stored procedure: First time takes 0.43341600 secs.

So in terms of time, first time they are very close.
Profiling comparison for both can be seen in below figure no_cache_comparison.png where left one is simple inline query and right one is stored procedure query.

There are some actions which are extra in the inline query:

1. freeing items
2. logging slow query
3. cleaning up

Running both second time retrieve data from cache taking
0.00048025 secs for simple query and 0.00036625 for stored procedure.

Profiling comparison for both can be seen in below figure cache_comparison.png where left one is simple inline query and right one is stored procedure query.

Here too there are some extra actions for inline query:

1. logging slow query
2. cleaning up

Taking into consideration the approximate equal timing of both the type of queries and that on second time execution both use cache, there is not much difference. But given the fact that stored procedures are compiled and stored, will be efficient for the db.

Also considering the article https://sachinkumaram.wordpress.com/2011/03/01/stored-procedures-advantages-disadvantages/, the advantages outweigh the disadvantages, I think.

Extra References:
http://www.seguetech.com/blog/06/04/Advantage-drawbacks-stored-procedures-processing-data
http://www.linuxjournal.com/article/9652

I would like to know your thoughts on this :)

Importance of MySQL cache

My test environment is:
Ubuntu 14.04 Trusty Tahr
MySQL Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)

MySQL uses sql cache to store results of queries that have been executed so that when the same query is executed again it retrieves the result data set from the cache instead of getting it again from db. So it is faster data access.

It is by default enabled in MySQL.

This is interesting since there is one question we ought to ask here whether we should use it or disable it or just leave it as it is who cares :).

Ok, moving forward today's session goals are:

1.     How useful is MySQL cache?
2.     When to use it and when not to use it?
3.     What to do if you do not want to use it?

There are some catchy areas here too like not all your queries will be stored in cache. For example, a query executed inside a stored function will not be stored in cache. Below doc link from MySQL further details out the specifics.

https://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

Starting with our goals:

1. How useful is MySQL cache?

It is very useful indeed as once you have the result data set, if the same query is executed again, the data is now fetched from the cache and it is much faster.

2. When to use it and when not to use it?

As per MySQL doc "If a table changes, all cached queries that use the table become invalid and are removed from the cache." https://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html .

So if you have executed a query and any content in any of the tables used in the query has changed (whether related or not to the dataset for the query), all the cache entries using those tables will be flushed and executing the same query again will fetch the data from the database.

So is this bad or is fine? Depends :).

If the query you are executing is very often executed lets say 5-6 times every minute and the related tables in database are altered very often lets say at a similar rate 5-6 times a second, so we even if all the queries are stored in cache but are ultimately flushed when related table data is changed, leading to fetching once again from database and storing in cache, but which we are never able to utilize.

So in this case if the table data is too often changed then it is better not to use cache for related queries since using cache adds a certain overhead (13% as stated below) to MySQL.

https://dev.mysql.com/doc/refman/5.5/en/query-cache.html

"If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower."

Otherwise if the table is less fairly changed and the query fetching the data is more frequent then it is more efficient to use cache.

3. What to do if you do not want to use it?

As discussed above, if you do not want to use it for a query which is not able to benefit the cache storage use SQL_NO_CACHE option while executing the query
which tells MySQL not to store the result set in cache.

Eg. SELECT SQL_NO_CACHE id, name FROM customer;

https://dev.mysql.com/doc/refman/5.5/en/query-cache-in-select.html

How to draw a smiley cookie face using html5 canvas?

How to draw a smiley face as shown below using html5 canvas.

Well, it's not as complicated as it looks. The picture includes below parts:

1. outer face circle
2. two circular eyes
3. one straight line nose
4. a smile arc

The above basic parts can be drawn using arc and moveTo methods of context object and it involves a little calculation as to where to place the sub parts so as to sync the whole face symmetrically.

Code:

<canvas id="smiley" width="800" height="600">

<script>
    drawSmiley();

    function drawSmiley() {
        var canvas = document.getElementById("smiley");
        var context = canvas.getContext("2d");

        // Draw outer face circle
        context.beginPath();
        context.arc(300, 300, 100, 0, degreesToRadians(360), true);
        context.fillStyle = "#ffffcc";
        context.fill();
        context.stroke();

        // Draw left eye circle
        context.beginPath();
        context.arc(250, 270, 15, 0, degreesToRadians(360), true);
        context.stroke();

        // Draw right eye circle
        context.beginPath();
        context.arc(350, 270, 15, 0, degreesToRadians(360), true);
        context.stroke();

        // Draw straight line nose
        context.beginPath();
        context.moveTo(300, 285);
        context.lineTo(300, 330);
        context.stroke();

        // Draw smile arc
        context.beginPath();
        context.arc(300, 330, 55, degreesToRadians(20), degreesToRadians(160), false);
        context.stroke();
    }

    function degreesToRadians(degrees) {
        return (degrees * Math.PI) / 180;
    }
</script>

I know the face looks a little out of place but you can do it better I know ;)

Click here for a demo.

How to draw a quarter circle arc using canvas?

Using canvas, we can draw a circle or a circular arc using the arc method of the context object.

The basic syntax of the arc method is:

context.arc(x, y, radius, start_angle, end_angle, direction)

Parameters:
x, y              are the center point co-ordinates
radius          (of the arc)
start_angle  Position where the arc should start (in radians)
end_angle   Position where the arc should end (in radians)
direction     Whether to start tracing in clockwise or anti-clockwise direction.  (bool)
                   true indicates anti-clockwise and false indicates clockwise direction.

So one can modify the start_angle, end_angle and the direction to trace any part of a circle. To draw a quarter circle arc, we need to start at 0 degree and end at 270 degrees but we need to trace in anti-clockwise direction so direction value would be true.

Relationship between radian and degrees:

2 * Pi  Radians = 360 degrees

Code for a quarter-circle arc:

<canvas width="600" height="400"></canvas>
<script>
    var canvas = document.getElementsByTagName("canvas")[0];
    var context = canvas.getContext("2d");

    context.beginPath();
    context.arc(100, 100, 75, 0, degreesToRadians(270), true);
    context.stroke();

    function degreesToRadians(degrees) {
        return (degrees * Math.PI) / 180;
    }
</script>


Click here for a demo.

How to draw a triangle using Canvas?

Canvas is basically a space in the web page where you draw graphics using javascript. Everything on a canvas is all about canvas.

So to start, lets have a canvas space over a 600 x 400 pixel area.

<canvas width="600" height="400">

The next thing we need to do is get a reference to the canvas element and get a context reference to it. Context is basically an object with methods and properties that allows us to draw on the canvas.

To draw a triangle, we basically have to start at a point and proceed along a line then move on to another point tracing along the path as we do so. The basic point is to first create a path and then stroke or fill it, so as to make it visible.

Below is the code to draw a triangle on a canvas:

<script>
    // Get dom reference to canvas element
    var canvas = document.getElementsByTagName("canvas")[0];

    // Get context reference to canvas
    var context = canvas.getContext("2d");

    // Begin a path with beginPath() method
    context.beginPath();
    context.moveTo(100, 150);

    // Traces a line path from current point to specified point
    context.lineTo(250, 75);
    context.lineTo(125, 30);

    // Closes the path by joining from current point to point where
    // path began first
    context.closePath();
    context.lineWidth = 5; // Sets the line width

    // Make the path visible by stroking it
    context.stroke();

    // Fill inside of the closed path structure with color
    context.fillStyle = "red";
    context.fill();
</script>


Click here to see a demo.

HTML5 CANVAS Bring out the inner artist in us

It's time to get our hands dirty with some colors and bring out the inner artist and creationist within us to fore. HTML5's CANVAS element provides us with a virtual canvas which is similar to the leather canvas which real world painters and artists use to draw wonderful art and what not.

Some use cases to which the CANVAS element can help us in:
1. To create a simple drawing structure like a circle, ellipse which may be part of a larger design.
2. To create an animation of written letters created slowly to create an illusion of an invisible hand writing.
3. To create html5 based games based on the idea of "update and loop" principle and "persistence of vision" concept.
4. To develop a platform for real-time creation of structures like charts, hierarchical box shaped structures one beneath other etc.

There are many more of which I am ignorant right now, but will update as soon as I know about it.

Stay tuned as we begin our journey as an artist, thinker, designer, creationist and what not :)

Handling errors while using html5 audio

While using the <audio> tag, there are chances of errors happening while playing the audio file.

It may be due to one of the below reasons:
- Intermittent connectivity
- Invalid "src" being set
- An audio format which the browser cannot decode and so cannot play
- Due to corrupted audio file being played

It would be better if we could know about these and be prepared to handle them instead of putting the user through a very bad experience.

The audio object has an error event which can be listened to for any errors. The "audio" attribute on the audio element can also be accessed to get the last error which had occurred. It returns a "MediaError" object representing the current error state of the element. It returns null if there is no error.

Types of error:
Each of the below error types has a corresponding associated number that is the error code produced by the "event".

- MEDIA_ERR_ABORTED(1)
    Occurs when the fetching process for the media resource was aborted by the user agent (browser) at the user's request.
    
- MEDIA_ERR_NETWORK(2)
    A network error of some description caused the user agent (browser) to stop fetching the media resource (after the resource was established to be usable).

- MEDIA_ERR_DECODE(3)
    An error of some description occurred while decoding the media resource (after the resource was established to be usable).

- MEDIA_ERR_SRC_NOT_SUPPORTED(4)
    The media resource indicated by the src attribute or assigned media provider object was not suitable.

Sample Code:
<audio src="sample.mp3"></audio> 
<script>     
    var audioElem = document.document.getElementsByTagName("audio")[0];
    
    // Add event to detect error and display error code      
    audioElem.addEventListener("error", function () {
        if (audioElem.error) {
            alert(audioElem.error.code);
        }
    }, false);
</script> 

References:
https://developer.mozilla.org/en-US/docs/Web/Guide/Events/Media_events#Error_handling
https://html.spec.whatwg.org/#error-codes