Skip to main content

Posts

Showing posts from April, 2018

Group by list doesn't require as selected list

When we run group by in mysql "group by list doesn't require as selected  list" . This is another myth and confusing for mysql users.

There is a table structure and some test data which I ran on mysql and sql server simultaneously.

Create table groupby_test ( id int,desg varchar (20), location varchar(20));

insert into groupby_test values (1,'Jr Software','Delhi');
insert into groupby_test values (2,'Jr Software','Noida');
insert into groupby_test values (3,'Software Software','Delhi');
insert into groupby_test values (4,'Sr Software Software','Gurgaon');
insert into groupby_test values (5,'Software Software','Gurgaon');

Then Executed below group by query on mysql and sql server.

select desg, location from groupby_test group by desg;

Result in MySQL;


Result in SQLServer

Msg 8120, Level 16, State 1, Line 9 Column 'groupby_test.location' is invalid in the select list because it is not cont…

Why does mysql processlist display list of sleep queries.

Hi, Many times mysql users asking me "show processlist showing list of many sleep queries on their environment". This post is for mysql user.

Sleep meaning that thread is do nothing.  Its not a query waiting for connection. It's a connection pointer waiting for the timeout to terminate. and it doesn't have an impact on performance .

To varify on your instance run below query..

show variables like '% wait_timeout%'  

Then we will see setting is  default wait_timeout=28800 or something else as configured;

So we can set values smaller,eg 10-20. We can also kill the thread status maked to sleep, that will not impact system.

Thanks

Why JSON Came into Picture, Part-2

Hi Friends,

This is the 2nd part of my post "Why JSON Came into Picture " .  I am trying to more elaborate how JSON replaces XML and supported in technology upgrade.

Background About JSON

JSON stands for JavaScript Object Notation, and was first formalized by Douglas Crockford. JSON is a data format interchange - method of storing and transferring data. Mostly its uses such as data conversion (JSON to SQL) and exporting data from proprietary web apps or mobile apps. XML was a big buzzword in the early 2000’s, JSON become the buzzword in later few years.

What are the Impacts JSON bring in technology ? NoSQL Document database became popular, mongodb was among one of lucky database vendor. Technology found JSON as an alternate to xml for data interchange on platform independent.Technology found a supporting for RDBMS to keeping variety of data specially non-structured data in one environment and module.Data explosion and big data came into the platform. The cost on data keeping h…