Looking to optimize "Group by" in MySQL?

Few months back I was asked for the reason behind the recommendation by the consultants from MySQL. When the system was performing poorly the consultants had walked in and simply requested them to add the "order by NULL" clause to the queries. To the joy of the client and amazement of the tecnical team at the client location, this simple fix had done the trick and suddenly there was a marked improvement in performance! This had left the technical team confused, as they started to wonder why and how this happened. WhenI heard this I was confused ( as always ;) ), as I could not think of any possible logical explanation for this behaviour.

Last week while I was reading the planet MySQL I bumbed into an article talking about order by NULL and I think I had found the possible reason. Any how it is not a statement, which would optimize all queries , instead it would optimize all the statements with a group by clause.

To make things simple lets take small example,

EXPLAIN SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode \G

This gives the following output,

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary; Using filesort

The extra column indicates that both filesort and temporary is being used, which is an indication of poor performance.

Alternatively when its applied with a Order by NULL as shown below,

EXPLAIN SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode order by NULL \G

gives an ouput,

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary

Which indicates a bit of an improvement as it does not any longer have Using filesort.

This addition of a order by NULL could give a performance increament by many folds at times.

The next most important question is how does this happen?

MySQL by default when having a group by statement would also order by the same column, which would require an additional sorting to take place. Any how when the order by NULL is included, it does not do a sort and thereby give a performance improvement.

Sir, but with love

From my childhood I had been bought up in an atmosphere where the "Sir" was used to refer the teacher's at my school and quiet rightly these people were those for whom we had great admiration for and looked up to as exemplary characters who molded us into what we are today. It’s true that this way of referring to my masters was something that was taught to me or even one could argue that this was a protocol which was forced on to me while I was kid. Whatever the view point the reader may have I still think that while being a kid this should be the way in which the system should continue because as kids its not possible for us to distinguish an ordinary person from a personality to be referred as "Sir" and also else as a kid we would never learn to respect people. Looking from where I presently stand I still call these great teacher’s of mine as “Sir” and with the same or even more amount of admiration and respect for the part they had played in my life. Probably this mentality still is in my mind as I had already done a benchmark of this term “Sir” at a very high level and being a servant in the software industry where the term “Sir” is frowned upon.

The sudden emergence of the practice in the local universities by the “temporary tutors”, who are just a batch or two senior to demand from the rest to refer them as “Sir” or “Madam” seems ironical. It’s an acceptable practice, if it’s a military establishment where you find a culture where people salute the ranks and not the person and as long as I know a university is not a military organization as long as it’s not a military university. I find that these people had been caught up in a “psychological illusion” where they consider them to be lecturers or even in the caliber of professors now it self. The real reason for this being the fact that unfortunately for many and fortunately for some we have a serious scarcity for qualified lecturers and you find that people fresh out from the degree very often doing lectures to degree students. Please note that I am not blaming these fresh graduates for teaching as I myself had been thought by some good teachers who were fresh graduates, but I am actually throwing caution at the people who had got blown out by this new temporary seat they had been assigned to.

The only thing that I can say to them is wait for the turn and at that point people will call you as “Sir” out of respect for the wisdom you posses or the personality you are or for charisma you posses and not to jump the gun to demand or rather in a way beg for respect, remember when you get the respect that you had demanded specially from a university student its just a temporary thing, it does not come from the heart.

No pun indented, no corners spared. Its just some musings from “sayy” so take if something is worth out of what is written or even don’t be hesitative to spit on me(I mean literarily ;) ) by the way of a harsh comment if you really feel that I deserve it for writing this.

Dealing with inactive listeners – my experiences

For presenters dealing with inactive listeners is one of the biggest challenges and process of brining back inactive listeners to the active state is an art in its own right. For some campus teachers this is a night mare and at the end of the day you find them depressed and really disturbed after a teaching session, when they feel that they had not succeeded in getting there point across to the audience. Some of the seasoned profs have chosen either of the two extremes, one extreme group, believes that, whatever they say they expect the students to listen or at least pretend to be listening by nodding, having eye contact and all, the other extreme group doesn’t really care about the audience at all and just preach whatever they want and doesn’t really care if they had got their point across or not.

The objective of this piece of writing is to make the presenter to be more objective in their expectations and analysis of there audience and thereby becoming more successful in getting there message across to their audience.

The Four major reasons for people seeming to be or really being inactive listeners

1. Environment
2. Cultural differences
3. Incompetence of presenter
4. Inherent human weaknesses

Environment

Awareness of the environment in which you are doing the presentation is of utmost importance; you should know who is participating in the session and have a general idea about what would be their knowledge in the topic that you are talking about.

Further more consider the physical factors like the following, is the stage properly organized? Is the projected content visible to everyone in the audience? Is the air-conditioning temperature at appropriate level? If using a microphone check and see if it’s correct? etc

When talking about the environment setup my mind goes back to my school days, when I was a senior student at college I used to go to my primary school to teach the kids to play chess, incidentally I was teaching in the same class that I had studied about ten years before. I was teaching the kids and suddenly I noticed a student, who was sitting in the same seat I used to sit years before, being the worst listener. I walked behind his seat casually and looked out of the window to know what was distracting him and then I remembered that my teacher, when I was the kid in that seat, always made it a point to have the window curtains down before she started the class and never told me the reason why it should be closed. At that time I can remember that this was something I hated to do, because the class was overlooking a lush green grass yard which was the playing ground for the monkeys and I wanted to watch them and closing the curtain was preventing me from doing what I liked, but only when I stood in the shoes of a teacher I realized her wisdom and from that day onwards I too made it a point to have the curtains down in that classroom before I started the sessions.

Culture

Understanding the cultural behavioral attitude of people is very important to understand the listeners. Just because a person does not look into the presenter’s eye that does not mean that the particular person is not listening to you.

A classic example of this happened on my first international class I did, I had audiences from an array of communities and there were a few ladies from cultures where they are not used to look in to the eyes of a man. I noticed that they never looked up at me and were focusing on the computer in front of them or at the book. Initially I was worried as I was clueless as I was not sure if they had understood what I was talking about. I played a bit of a mind game with them to get them out of there usual behavior. I first got them to look at the content that was being projected on the screen, by showing irresistible content which is pertinent to the audience. This was a confidence building exercise, although it was a slow process I managed to bring them to a state where they lifted the face to look at the projector and at this time I showed a lot of energy in my presentation and in a few minutes time, when they had enough confidence in my behavior they were behaving like any other audience. The lesson learnt was that when it comes to measuring listeners, we should not use the common measurement for all people; we should appreciate the uniqueness of each individual.

Incompetence of Speaker

This has two major categories, one where you find the speaker is incompetent due to his lack of knowledge in the topic he is speaking about or lack of preparation, the second major category is where the audience has a premature feeling that the speaker is incompetent due to his outer look or age.

In order to convince that the presenter has enough competence the presenter will have to make sufficient preparation on the areas he is going to talk about and show a lot of energy and commitment, which could make the audience, change their initial perception on the presenter.

I can remember on a few occasions when I was introduced as an expert in a certain domain the people looked at me in a very sarcastic way because of my age, any how I knew this was the main challenge that I will have to face and was ready for it. Like a typical test batsman I would put the head down and bat and the only way in which I could show that I could prove myself was my proving that I have the knowledge although at a lesser age than a typical expert.

Inherent Weaknesses

Be informed that any human being will loose focus when you are doing a long session and its quiet natural for people to switch off. Think from the perspective of the listener if you were the listener how long will you be able to keep focus on a single task. The most challenging thing for a presenter is to master the art of facilitating the return of an inactive listener or a listener who had lost focus, when you find that a listener is making an effort to return to the presentation, make it a bit easy by doing a quick recap on what you are doing or talking about. Especially if you had given a hand out point out the slide you are now in.

Most of the reasoning for the inactive listening resolves around the above four categories.

After identifying the problem we should consider the possible solutions;

I provide below a few handy tips to minimize inactive listening.

Solutions

Hint 1:

Drive the entire audience as a team; use each others energy to sustain the interest among the audiences.

Hint 2:

Relate to things people closely associate with, for example there job.

Hint 3:

Use the natural competitive instinct of people.

Hint 4:

Ask question that you know the users know the answer to ease them up and make them to be more at home.

Hint 5:

Use Humor, but remember this should be good clean humor and always know that not all people are 100% receptive to humor. Refrain using humor involving race, colour, religion and gender.

Hint 6:

Encourage the right type of participation, when someone asks the right type of question cohesive to the content you are talking about, explain the question to the audience and then answer the question.

Hint 7:

Spur up some irresistible topic which will make them get involved.

Hint 8:

Spring a few surprises relevant to the content.

Hint 9:

Manage the time well.

Hint 10:

Make it look as if the thing that you are talking about is the simplest thing on the planet. Specially, when you want to make a product presentation or a teaching lesson.

*** Incidentally the above was the main content one of the presentations I did to my colleagues at office at their own request and I can feel that it had made a paradigm shift in the way they did and dealt with people during the presentation.

watch out - md5 sum bug

The other day I was in for a rude surprise when it was reported that in the application which we had developed, the authentication at times fails. The application required to auto generate an md5 encrypted value for authentication and it was found that md5 value generated was dropping the leading zero's when ever the actual md5 sum value was supposed to have leading zero's.

It was a rude shock for me.

This was the piece of code that I had,

String unencryptedValue= "test"; MessageDigest m= MessageDigest.getInstance("MD5"); m.update(unencryptedValue.getBytes(),0,unencryptedValue.length());
String encryptedValue = new BigInteger(1,m.digest()).toString(16);

Then it took me some time to realize that the BigInteger casting was causing the havoc, and at the same time waruna gave me a link to the following blog http://jdwyah.blogspot.com/2006/08/java-md5-password-hash.html, which has the solution for the above problem,

its just a matter of introducing another method which would pad the leading zero's to the md5 value to make up the required length.

the fixed code looked something like below,

String unencryptedValue= "test"; MessageDigest m= MessageDigest.getInstance("MD5"); m.update(unencryptedValue.getBytes(),0,unencryptedValue.length());
String encryptedValue = new BigInteger(1,m.digest()).toString(16);
encryptedValue = fixLeadingZeroBug(encryptedValue, 32, '0');

public String fixLeadingZeroBug(String s, int length, char pad) { StringBuffer buffer = new StringBuffer(s); while (buffer.length() <> buffer.insert(0, pad); } return buffer.toString(); }

It was just now a matter of fixing the bug and instead I learnt an important lesson to be a mindful of these leading zero's when ever an integer is used in place of having it as a String for intermediate processing. But unfortunately I had to learn it the hard way. :(

Factory Method

The third in the series was the factory method, was rather a simple pattern to present. Any how for the last two weeks (thanks to vanky) I have been having the Head first pattern book with me. This provided me with a nice peace of example which I could use for the presentation.

I had a big surprise at the presentation when I suddenly found that the presentation that i was making was being recorded on video. Probably I should be more careful when i make my next one as I leaving evidence of all the lies I say at the presentation.... haha

Composite pattern

I had the opportunity of doing my second presentation of the Design pattern series of presentations which my self and Vidula were doing alternatively. This time it was the Composite pattern that I had to present on. Got a bit too busy and the slides which I created for the purpose was something I didn't feel proud about, it was a bit incomplete and I just scrapped through explaining the essentials of the pattern in a half an hours time.

And then came the question of the day! Harsha presented the audience and myself with a question to try.

He simply asked us to use the composite pattern to solve a search problem????

"The question"

Suppose you have a gui like follows,
How is it possible to use the composite pattern to create the where clause of the sql statement dynamically?

It was a nice question to make us think about the composite pattern and its practical application.

This in turn led to an interesting discussion on topic.

The solution to this, as explained by romith, was to have the AND and the OR as the nodes and the criteria like the Name , City etc as the leaves!

Initially I thought that the Name, City and NIC could be made into nodes and have the AND and OR as the leaves.It was quiet rightly pointed out by Harsha that it would work , but was not the correct answer, as it was not a scalable solution and having it the other way around would provide with an extensible and a scalable solution.

I ended this presentation with a lot of self satisfaction as it was a presentation that I left with a clear picture about the topic than the idea that I had at the start of it! But ironically I was the presenter.. haha poor audiance!

Abstract Factory Pattern

Along with Vidula, I had been assigned to do a series of tech talks on design patterns. Our initial objective is the cover the eight software design patterns that has been deemed to be most important. I will cover four of these patterns and hope to have the slides on the blog after I complete the tech talks. In the series of these tech talks the first was on abstract factory pattern and here I have attached the slide along with this blog.

At the end of the presentation some interesting aspects and perspectives on this was discussed.

Error opps "errors"

First a staring error in the diagram was pointed out by Thilina, where one of the concrete factories was not shown to have been extended from the abstract factory, my apologies for not noticing that!

Any how I will not accept the other error that was pointed out, where it was said that piece of code was misleading in the main method, since I have not handled any other cases(when its not EU or Canada). Unfortunately I feel that code that I write on slide for a presentation is not for the purpose of code review, but is intended to be coherent with my overall objective of getting the usage of the abstract factory pattern to the audience. Further more I had to consider the space that I have on each slide to play around with , probably if my overall objective was to explain the usage of the if else statement to a group of amateur programmers then I would have accepted that I had made a glaring error in not handling the other case and logging it J

Usage of the abstract factory pattern in a spring application

Spring being service oriented architecture enables the injection of the objects which implements the defined service interfaces. It just does the job of the abstract factory pattern and in the words of Harsha it is almost 80% of the abstract factory pattern, any how a purist could argue that it’s not an abstract factory as it does not comply with all the requirements of being an abstract factory.

Switching between different kinds of message sending implementations

A common scenario that we face is having various message sending mechanisms to choose from and is required to switch as and when required for example from email to sms or ussdc …. The question was this if abstract factory was appropriate for this purpose? At the outset it might sound to be appropriate, any how if considered with respect to the other available options it was suggested that the strategy pattern might be more appropriate.

A wait the next design pattern slides!

Google , Yahoo and Microsoft does it matter to me?

Skimming through the news papers I was in a rude shock to see that Google is coming to Yahoo's rescue at the face of a hostile bid from Msft. This made me wonder why people say that there aren't any permanent fores or friends only in politics, business at the highest level is also seeming to be making strange bedfellows.

The history behind this present situation is very interesting and plenty has been written about it and analyzed about these three companies, Google, Yahoo and Msft. Msft has been the forefront of providing user friendly but proprietary OS and its chairman has been topping the worlds richest list since Msft became a household name in the early part of 90's.

Yahoo, had its humble beginnings as ones hobby went onto become mother of all Internet based companies established itself in the forefront of Internet marketing, till the emergence of a start up in the name of Google, who fought a rough battle with Yahoo and incidentally dethroned it from its established position to become the leader. The tactic of having contextual marketing and along with it a more powerful search algorithm against a business that was already established.

Being just another observer of this battle for some time I have my own thoughts on the matter. I had always been mesmerised by the rise of the Google and happy to see that it is a good example of how simplicity can succeed even in the very dynamic business environment, specially its simplicity in interfaces and the meteoric rise it has gone through from its humble beginnings had always been things that I had been interested about. Any how personally does not like the idea of yahoo being brought under its wings, if we are to have a sustainable Internet market probably we need a healthy competition and not a monopoly. Only in that case will be the users like us will be befits from the occasional spills they make in the event of cut throat competition. I can remember the way the web mail account size suddenly started to burst and come to age! That happened for no other reason than competition.

But on the other hand yahoo falling pray to Msft is worse than that! So probably from a users perspective I would love to see the mooted partnership between yahoo and Google might be the best for us, as its obvious that yahoo is not in a position to convince its investors unless it comes up with some sort of a viable strategy to generate sufficient cash in the fore see able future.

Intelligent Ant

You might have heard about swarm intelligence where a group of ants acting as in concert exhibit intelligent behavior. But when these ants are considered individually they do not normally exhibit any intelligent behavior. But the other day I was in for a strange surprise when the hsenid ant individually exhibited intelligent behavior, when it was placed in captivity and harassed by tying objects on its sensors, it reacted correctly to suit its hostile environmental condition.

The news reaching us from sources related to the ant says that the ant is planning a great escape from its abductor and might end up in one of the other tables nearby, where it can have its eye open!

Playing around with memory tables

The other day I was discussing about using the alter table statement in MySQL to convert from MyISAM to Memory engine with some of my colleagues. We were just wondering what will really happen if we try issue an alter table statement, which will subsequently try to create a memory table which will have a table size larger that the max_heap_table_size parameter. We were wondering if data truncation will occur after that limit.

So I thought of testing it and I did the following,

initially I set the following in my.cnf
[mysqld]
max_heap_table_size = 8K

then I tried the following,
alter table City2 engine=memory;
ERROR 1114 (HY000): The table '#sql-2bc1_1' is full

and as soon as I checked
show create table City2;
City2 | CREATE TABLE `City2` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

It was clear that no changes has taken place and the alter table has completely failed and the table continue to be MyISAM,

After that just to confirm it I did the following,

set global max_heap_table_size=2*1024*1024;

alter table City2 engine=memory;

City2 | CREATE TABLE `City2` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MEMORY AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

So finally I know for sure that there will be no data truncation when you issue an alter table statement to convert to Memory. Hopefully this will be useful to the guys who are playing around with memory tables these days :)

PS: I tested this on mysql 5.0.27-max-log

Copying tables in MySQL

The other day I was asked by friend on how a table could be copied in mysql, this query made me think that I should list down some interesting yet simple statements which might be important to people.

QUERY 1: If you want to create a new table out of an existing one

create table City2 like City;

This would create a new table with the same structure as City, but the rows in City will not be copied to City2.

QUERY 2: If you want to create a new table along from an existing table along with all the existing data.

create table City3 select * from City;

QUERY 3: If you want to select only a few columns and create a new table from an existing one

create table City4 select Name,CountryCode from City;

QUERY 4: If you want to copy all data from one table to the other

insert into City2 select * from City;

QUERY 5: If you want to copy certain data from one table to the other

insert into City6(Name, CountryCode) select Name, CountryCode from City;

** NOTE in this query we do not have a values part in the insert query.

Eating a humble pie or rather a thosai

Since my childhood days eating thosai was the last thing i wanted to do. Usually ammaa making a thosai was a reason for me to protest and turn the house upside down. Ammaa knowing this fact made sure that she made some alternative food for me when she made thosai for dinner for the rest at home.

But strangely when ever I come down to KL I am made to survive on thosai as thats the most pure vegetarian food that I could find! What a turn of fate, All my life I have been too selective and resisted eating the best thosai which could ever possibly made and now I am made to survive on a very bad version of it! Adding insult to injury I am learning the foolish "art" of eating thosai with folk and spoon, but probably the only positive that happened out of this was that I spent a very long time trying to eat it and eventually my mind started to wander into the past and think about the irony of me eating a humble thosai!

Ammaa surely would love this and would be laughing and would be wondering what has happened to all my demands. So probably to cover up my slip I should say that thosai in KL is tastier, but telling a lie is easier but putting it on paper is impossible.

Student Strikes - On facebook

The other day I was speaking to a foreigner and I fell into an awkward position trying to explain the concept of student strikes in the Sri Lankan universities, which was quiet alien to him, he said "I do not understand the concept of student strike, they are supposed to be at the university at the tax payers expense to study, so why do they strike?".

He continued, if studying is not their priority then they must be freaking students who should be kicked out. I am looking for answers to give him, if i am asked about this again?

Should I say we strike to cover up the sins of our fellow so called "colleague/s" against whom disciplinary action has been taken?

Should I say we our just pawns in a larger political board game?

Should I say that becoming a university student gives us the luxury of wasting the tax payers money at will and then demand for more?

Should I say that we have not prepared for the exam yet and strike is the device used to get the exams postponed?

or should I say that my university is closed because we ran out of toddy?

my intention is not to ridicule the learning organizations, but to make students think on a bigger scale and to make the students to break the shackles and come of out of the mental prison that they have been caged in by systematic brain washing.

I wrote the above and started a discussion on facebook, check out how this proceeds!
http://www.facebook.com/topic.php?uid=3056930281&topic=5018&ref=mf

Tribute to Flori

The first and the most cherished cat of our home passed away on an April fools' day 8 long years ago.

Her remembrance, brings back sweetest memories.

Though we raised many a cat not a single came close to her braveness, cuteness or bestmanners. She made even her formidable opponents(cat opponents, including appa) love and pet her.