Order By - in SQL

ms_user table

user_id username email area_id
1 sherlock c_sherlock@email.com 1
2 poirot poirot@email.com 2
3 watson b_watson@email.com 1
4 hasting hasting@email.com 2
5 mycroft a_mycroft@email.com 1

SELECT * FROM `ms_user`
ORDER BY `area_id` , `email` DESC , `username`;

Continue Reading »

Google search basics

* Phrase search (”")

    By putting double quotes around a set of words, you are telling Google to consider the exact words in that exact order without any change. Google already uses the order and the fact that the words are together as a very strong signal and will stray from it only for a good reason, so quotes are usually unnecessary. By insisting on phrase search you might be missing good results accidentally. For example, a search for [ “Alexander Bell” ] (with quotes) will miss the pages that refer to Alexander G. Bell.

* Search within a specific website (site:)

    Google allows you to specify that your search results must come from a given website. For example, the query [ iraq site:nytimes.com ] will return pages about Iraq but only from nytimes.com. The simpler queries [ iraq nytimes.com ] or [ iraq New York Times ] will usually be just as good, though they might return results from other sites that mention the New York Times. You can also specify a whole class of sites, for example [ iraq site:.gov ] will return results only from a .gov domain and [ iraq site:.iq ] will return results only from Iraqi sites.

Continue Reading »

Insert Ignore - Mysql Query

The new value is not inserted if there is a duplicate key.

syntax ::
INSERT ignore into T values(a, b, c)
INSERT ignore into T(a, b, c) SELECT a, b, c FROM F

*TABLE must have a primary key.

DISTINCT and GROUP BY

A DISTINCT and GROUP BY usually generate the same result, so performance should be the same across both query constructs.

the diff ::
Use “Group by” when you use
aggregate query, and “DISTINCT” when you want to distinct values.
Essentially it is just a matter of syntax in this case, the end result should be same.

summary :
some people said that “DISTINCT” execute faster than the “GROUP BY”, because GROUP BY performs appropriate sorting before returning the results. CMIIW

Update vs Insert in SQL

PostgreSQL 7.03

Normal autocommit on fsync off
insert | update insert | update insert | update
5:16 | 6:46 n/a | n/a 0:10 | 0:18

“Insert” is faster than “update”.

     |  1  |  2  | 3 |  4  |  5  |  6  |  7  |  8  |