Notes about mysql indexes

Look at this answer on how mysql indexes work on stackoverflow.

Basically an index on a table works like an index in a book (that’s where the name came from):

Let’s say you have a book about databases and you want to find some information about, say, storage. Without an index (assuming no other aid, such as a table of contents) you’d have to go through the pages one by one, until you found the topic (that’s a full table scan). On the other hand, an index has a list of keywords, so you’d consult the index and see that storage is mentioned on pages 113-120,231 and 354. Then you could flip to those pages directly, without searching (that’s a search with an index, somewhat faster).

Of course, how useful the index will be, depends on many things – a few examples, using the simile above:

* if you had a book on databases and indexed the word «database», you’d see that it’s mentioned on pages 1-59,61-290, and 292 to 400. In such case, the index is not much help and it might be faster to go through the pages one by one (in a database, this is «poor selectivity»).
* For a 10-page book, it makes no sense to make an index, as you may end up with a 10-page book prefixed by a 5-page index, which is just silly – just scan the 10 pages and be done with it.
* The index also needs to be useful – there’s generally no point to index e.g. the frequency of the letter «L» per pag

Continue reading “Notes about mysql indexes” »

mysql sort table which contains null value

Imagine the following table A:

Name Surname Code
Giorgos Komninos 3
Vassilis Antonopoulos NULL
Lazaros Papadopoulos 1

and now if we use the following sql command:

select * from A order by Code

the result would be:

Vassilis Antonopoulos NULL
Lazaros Papadopoulos 1
Giorgos Komninos 3

but we want those records with NULL Code to be in the end .

Here is the solution:

select * , Code is null as isnull from A order by isnull asc, Code asc

Then the result would be:

Lazaros Papadopoulos 1
Giorgos Komninos 3
Vassilis Antonopoulos NULL

Simple python script for mysql backups

Here is a small script i wrote in python for making quick backups and restores for mysql dbms.
It’s very simple with a few minor bugs.
Continue reading “Simple python script for mysql backups” »