UNION Example MySQL

There are 2 tables

Table A

id
1
2
3

Table B

id
1
2
3
4
5

Our job is to merge 2 tables to get following output

We fire our sql query as follows

SELECT  `a`.`id` AS `id` FROM `a` UNION SELECT `b`.`id` AS `id` FROM `b`
and get desired output

There are some rules that you need to follow in order to use the UNION operator:

• The number of columns appears in the corresponding SELECT statements must be equal.
• The columns appear in  the corresponding positions of each SELECT statement must have the same data type or at least convertible data type.

By default, the UNION operator eliminates duplicate rows from the result even if you don’t use DISTINCT keyword explicitly. Therefore it is said that UNION is the shortcut of UNION DISTINCT.
If you use UNION ALL explicitly, the duplicate rows, if available, remain in the result. The UNION ALL performs faster than the UNION DISTINCT.

Mysql Joins

Joins are used to get data from Non Clustered indexes in a table

Following are the Types of Joins

Inner Join

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja 

FULL OUTER JOIN

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga       
null  null       3     Darth Vader

LEFT OUTER JOIN

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

RIGHT OUTER JOIN

SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----

null  null     1     Rutabaga  

1   Pirate     2     Pirate

null  null     3     Darth Vader 

3   Ninja      4     Ninja

Clustered and Non Clustered Indexes

Non-clustered

The data is present in arbitrary order, but the logical ordering is specified by the index.

The data rows may be spread throughout the table regardless of the value of the indexed column or expression.

The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).
In a non-clustered index:

• The physical order of the rows is not the same as the index order.
• Typically created on non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.
• Index is stored separately and data is stored separately
• Data is not determined as a physical order

There can be more than one non-clustered index on a database table.

 Example: Index in a textbook

Non Clustered Index on Salary_grade in following table

Clustered

Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table.

Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.

Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required.

The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s). Create an object where the physical order of rows is the same as the index order of the rows and the bottom (leaf) level of clustered index contains the actual data rows .

 For example Primary Key Constraint in a table no matter if you enter data in an non sequential order it will always be retrieved sequentially as per clustered index

There can be only one Clustered Index in a table how ever there can be more than one columns in a clustered index in a table thus making it into a composite index.

Example: Telephone Directory

Non Clustered index on salary_grade in following table

Salary_grade      Last_Name            First_Name
2                        Timberwood            Jack
2                        Bibleton                   Mike
5                        Rabbies                    Luna

Clustered Index on salary_grade in following table

Salary_grade    Salary
2                       2000
5                       5000

Types of Indexes in a database

Bitmap index

A bitmap index is a special kind of index that stores the bulk of its data as bit arrays (bitmaps) and answers most queries by performing bitwise logical operations on these bitmaps. The most commonly used indexes, such as B+trees, are most efficient if the values they index do not repeat or repeat a smaller number of times. In contrast, the bitmap index is designed for cases where the values of a variable repeat very frequently. For example, the gender field in a customer database usually contains two distinct values: male or female. For such variables, the bitmap index can have a significant performance advantage over the commonly used trees.

Dense index

A dense index in databases is a file with pairs of keys and pointers for every record in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key.

Sparse index

A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block.

Reverse index

A reverse key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.

 

Request data from a server in a different domain

To request data from a different domain we use json with padding

or jsonp(json with padding)

we use Response to come in a callback function

Refer to following urls

http://itfeast.blogspot.in/2012/07/cross-domain-request-using-jquery.html
http://en.wikipedia.org/wiki/JSONP
http://api.jquery.com/jQuery.ajax/

script = document.createElement(”script”);
script.type = “text/javascript”;
script.src = “http://www.someWebApiServer.com/some-data”;

We get data like this

<script>
{['some string 1', 'some data', 'whatever data']}
</script>

Due to inconvenience in fetching data from script tag jsonp was introduced as follows

script = document.createElement(”script”);
script.type = “text/javascript”;
script.src = “http://www.someWebApiServer.com/some-data?callback=my_callback”;

Now since we have passed a parameter to return jsonp data we will always get sth like

my_callback({['some string 1', 'some data', 'whatever data']});

in the script tags     

Let's take an simple example of using the twitter feed.

RAW javascript demonstration (simple twitter feed using jsonp) 

<html>
    <head>
    </head>
    <body>
        <div id = 'twitterFeed'></div>
        <script>
        function myCallback(dataWeGotViaJsonp){
            var text = '';
            var len = dataWeGotViaJsonp.length;
            for(var i=0;i<len;i++){
                twitterEntry = dataWeGotViaJsonp[i];
                text += '<p><img src = "' + twitterEntry.user.profile_image_url_https +'"/>' + twitterEntry['text'] + '</p>'
            }
            document.getElementById('twitterFeed').innerHTML = text;
        }
        </script>
        <script type="text/javascript" src="http://twitter.com/status/user_timeline/padraicb.json?count=10&callback=myCallback"></script>
    </body>
</html>

And Basic jQuery example (simple twitter feed using jsonp)

<html>
    <head>
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
        <script>
            $(document).ready(function(){
                $.ajax({
                    url: 'http://twitter.com/status/user_timeline/padraicb.json?count=10',
                    dataType: 'jsonp',
                    success: function(dataWeGotViaJsonp){
                        var text = '';
                        var len = dataWeGotViaJsonp.length;
                        for(var i=0;i<len;i++){
                            twitterEntry = dataWeGotViaJsonp[i];
                            text += '<p><img src = "' + twitterEntry.user.profile_image_url_https +'"/>' + twitterEntry['text'] + '</p>'
                        }
                        $('#twitterFeed').html(text);
                    }
                });
            })
        </script>
    </head>
    <body>
        <div id = 'twitterFeed'></div>
    </body>
</html> 

Difference in Buffer and Cache

Buffer:
In computing, a buffer is a region of memory used to temporarily hold data while it is being moved from one place to another. Typically, the data is stored in a buffer as it is retrieved from an input device (such as a keyboard) or just before it is sent to an output device (such as a printer). However, a buffer may be used when moving data between processes within a computer. This is comparable to buffers in telecommunication. Buffers can be implemented in either hardware or software, but the vast majority of buffers are implemented in software. Buffers are typically used when there is a difference between the rate at which data is received and the rate at which it can be processed, or in the case that these rates are variable, for example in a printer spooler.


Cache:
In computer science, a cache (pronounced /ˈkæʃ/, like "cash" [1]) is a collection of data duplicating original values stored elsewhere or computed earlier, where the original data is expensive to fetch (owing to longer access time) or to compute, compared to the cost of reading the cache. In other words, a cache is a temporary storage area where frequently accessed data can be stored for rapid access. Once the data is stored in the cache, future use can be made by accessing the cached copy rather than re-fetching or recomputing the original data, so that the average access time is shorter. Cache, therefore, helps expedite data access that the CPU would otherwise need to fetch from main memory.

Constants,Configuration Variables in cake php

Many a times we need to use some variables throughout our application.

CakePHP's Configure class can be used to store and retrieve application or runtime specific values. This class can be used to store anything and can be accessed from any part of the application

For the application of the same we define a file in app/config/config.php

To store a configuration variable:

Configure::write('variable_name', 'value') ;

And to read from configuration:
 
Configure::read('variable_name');

Now we can use Configure::write() or we can also use the $config array

as follows

// Create the config array

$config = array();

// Define config variable

$config['from_email'] = '';

//  Or Define variable as follows

Configure::write('from_email', 'abc@abc.com') ;

 

$config['api_key'] = 'http://www.example.com';

$config['default_timezone'] = 'Europe/London'; 
$config['page_limit'] = 6;


Now our Framework must know about this file so add the following code in /app/config/bootstrap.php

Configure::load('config');


Now read the configuration variables anywhere in the application as follows

Configure::read('from_email');
Configure::read('api_key');
Configure::read('page_limit');