MySQL: Query Optimization Tips Part I

Writing SQL query is easy but it can use lots of server resources which is utmost important for huge databases, where minimization of execution delay is important, like Facebook – if they don’t update your status very frequently it will cost them users. Let’s start:

  1. Most important for query optimization is INDEX. Make sure you define index in your table for the columns which you use more often than others or create for every possible column.
  2. Use EXPLAIN statement to determine which indexes are used for a SELECT statement.

These were the general tips. For speeding up queries on MyISAM tables:

SELECT queries

  1. Use ANALYZE TABLE after loading data on it. This updates a value for each index part that indicates the average number of rows that have the  same value. For unique obviously it is always 1. MySQL uses this to decide which index to choose when JOIN is used.
  2. Sorting a table by index is good when reading all rows of the table according to the index.

WHERE clause

  1. Removal of unnecessary parentheses. eg:
    (a AND b AND c) OR (a AND b AND c AND d)

    instead of

    ((a AND b AND c) OR (a AND b AND c AND d))
  2. Constant folding eg:
    b>5 AND b=c AND a=5

    instead of

    (a<b AND b=c) AND a=5
  3. Constant expression used by indexes are only used once so try to use them as more as you can.
  4. COUNT(*) without WHERE clause on a single table fetch the result directly from MyISAM table information which is pretty fast.
  5. Constant tables are read first before any other tables. Constant table are:
    • Empty tables or table with one row.
    • A table with which WHERE clause is used on PRIMARY KEY or UNIQUE index.
  6. JOIN statement is slower that any other. It checks all the possibilities for join to give best result. If ORDER BY or GROUP BY is used and all the columns come from same table then this table is preferred first in joining.
  7. In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.
  8. LIMIT also executes faster than query without it.

I hope you found this helpful. We’ll continue with INSERT statements optimization in next post.

MySQL: Good Efficient Structured Database

MySQL is a well known name in RDBMS world and many of you must have used it. It implements one of the oldest way to create a database. I have never used it with any other application but web though. To run MySQL I use phpmyadmin, the browser application made for ease. We will be discussing some settings for MySQL other than default.

Required: phpmyadmin ( if you can use it from command line good for you! 🙂 )

  1. InnoDB vs MyISAM: These two are storage engines which are widely used. Facebook use InnoDB storage engine but they use their version of MySQL which is like more that 10,000 times more efficient than the default. They have optimized it for write operations (writing in database). We’ll see the difference between two (not all but major) and you choose which is best for you.
    • MyISAM – Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.The maximum number of rows supported amounts to somewhere around ~4.295E+09 and can have up to 64 indexed fields per table. Both of these limits can be greatly increased by compiling a special version of MySQL. Use the option –with-big-tables while creating table.Text/Blob fields are able to be fully-indexed which is of great importance to search functions.Number of indexes can be 64 in a table by default. You can increase it anything up to 128 using –with-max-indexes=N, where N < 128 while compiling MySQL or simply when you run it.Concurrent inserts are supported. Only condition there are no free blocks i.e., a result of deleting rows or an update of a dynamic length row with more data than its current contents.You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options. This is sort of prerequisite for huge databases.Table locking is present so concurrent writes are not possible.
    • InnoDB – It is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.Consistent nonlocking reads increase multi-user concurrency and performance.Stores user data in clustered indexes to reduce I/O for common queries based on primary keys.It supports Foreign Key which is of great use if  you don’t want to check the values being inserted in table which derives from different table.Designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.Maintains its own buffer pool for caching data and indexes in main memory.

      Stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions).

      Row lock is present so simultaneous writes on a table is possible.

  2. To set auto increment value for primary key but with default starting value other than 1 go to “OPERATIONS” tab for the table change the auto increment value to whatever you like say 1000000
  3. Go to the home page of phpmyadmin and click “PRIVILEGES”. You can assign different privilege to users that are using your database here. When you’ll add the user you can set privileges for him at the same time.
  4. “ENGINES” tab on your home page will tell which engines are installed on your machine.
  5. “VARIABLES” tab is the configuration which is currently set. You can change these variables for example: big table is “off” by default, you can switch it on and can increase the number of rows in a table to twice. Though not useful for InnoDB.

I personally use InnoDB, though it doesn’t matter for the amount of data I have. So, these were the few things which I believe can surely help you in creating a good database structure. Engines plays the most important role that’s why it was discussed in detail. I highly recommend you to read manual for MySQL.

Refreshing a particular div

With the evolving and more dynamic web application. Refreshing particular part of the page is very much required. Recommended ads on Facebook use it. When we click on like ad, it sends the server tag of the ad the server recommends you another ad. The use of it I can think of is for Views. Create a view of different object on your page and call them using load method.

Required: jquery-latest.js

Using:

&lt;script type=&quot;text/javascript&quot; src=&quot;jquery-1.6.js&quot;&gt;
&lt;/script&gt;
&lt;script type=&quot;text/html&quot;&gt;
      $('#newcontent').load('file2.php');
&lt;/script&gt;
Explanation:
       # is selector for id which is newcontent, a div here. Load method loads the content of file2.php in newcontent div.

Wolfram Alpha: Its OMG

Wolfram Alpha was launched in May’09. Its not a search engine, its “answer” engine. It was made by Wolfram research, founded by Stephen Wolfram( child prodigy I must say, read about him on wikipedia ), a mathematician and physicist.

This site is amazing, it is an online service that answers factual queries directly by computing the answer from structured data, rather than providing a list of documents or web pages. You type in query like “e^2” and it gives you answers in graphs, value etc. if you type in “chocolate” it will give its nutritious facts. This site amaze me from the very first time I have seen it and to be more specific the answers it gives.

They have APIs waiting to be used and give some awesome website. You can create a Pro account for more benefits. I personally feel this thing can do something to internet what Hotmail did to email.

Pinterest: New buzz

Pinterest is a new website launched in March’10. Instead of going the traditional way, it used people’s interest as its base.

Login is by invite only for now. You can pin your interests, see and re-pin other people’s interest, follow people having same interest. It do not give the social experience which Facebook gives but its of great use for companies and individual products. They can conduct survey for their products or analyse their products.

There is also one thing I would like to focus on- design. Look at the vertical layout, jQuery plugin Masonry gave it that look. Its kind of nice and new design.

They have APIs for sharing and all. Lets see who comes up with something new using it. Try it, see if you like it.

Socket Programming: Handling multiclients

Referring to previous post, we will continue with same code. In this post we will try to handle multiple clients.

Multiple clients means that multiple client programs can connect to server program. For this we will use threads. Threads are nothing but process and runs only the part that is required (we decide what is going to run) to be run.

There will be a minor change in both server and client programs.

Required: Python 2.6+

Using:

server.py

#!/usr/bin/python

# Import all from module socket
from socket import *
#Importing all from thread
from thread import *

# Defining server address and port
host = ''  #'localhost' or '127.0.0.1' or '' are all same
port = 52000 #Use port &gt; 1024, below it all are reserved

#Creating socket object
sock = socket()
#Binding socket to a address. bind() takes tuple of host and port.
sock.bind((host, port))
#Listening at the address
sock.listen(5) #5 denotes the number of clients can queue

def clientthread(conn):
#infinite loop so that function do not terminate and thread do not end.
     while True:
#Sending message to connected client
         conn.send('Hi! I am server\n') #send only takes string
#Receiving from client
         data = conn.recv(1024) # 1024 stands for bytes of data to be received
         print data

while True:
#Accepting incoming connections
    conn, addr = sock.accept()
#Creating new thread. Calling clientthread function for this function and passing conn as argument.
    start_new_thread(clientthread,(conn,)) #start new thread takes 1st argument as a function name to be run, second is the tuple of arguments to the function.

conn.close()
sock.close()

Use the same client.py code for client from previous post.

#!usr/bin/python
from socket import *

host = 'localhost' # '127.0.0.1' can also be used
port = 52000

sock = socket()
#Connecting to socket
sock.connect((host, port)) #Connect takes tuple of host and port

#Infinite loop to keep client running.
while True:
    data = sock.recv(1024)
    print data
    sock.send('HI! I am client.')

sock.close()

Understanding :

Highlighted code is the changes done in the previous code we had.

What we have done in server.py is we took the communicating part inside the infinite while loop of function clientthread and using it in thread on line 32.

In client.py we took the communicating part inside the infinite while loop.

Everything else is explained in comments.

Note:

  • Programs will not terminate by themselves because they will never come out of loop. So certainly you will receive error that socket is already in use.
  • start_new_thread() takes 3 arguments. 3rd argument is kwargs which is of no use right now.
  • 2nd argument of start_new_thread has to be a tuple. Tuples are fixed length array in python.
  • Module threading can also be used for threading.
  • start_new() is identical to start_new_thread().
  • 5 in sock.listen(5) in server.py is of no use as every client will interact with server. May be it  will be required when server can not handle more process.

Socket Programing: Python

We will be understanding the basics of socket programming using Python.You may be thinking why socket programming? Well, its because if you need to send data over a network you need to know about socket. The HTTP websites runs on port 80. Each website has a IP adress. So when you request for a website actually your browser is trying to get data from someipaddress:80. 80 is default port that browser uses, otherwise specified. You might have used Apache. What it does it creates server(we call apache server) and binds it to 127.0.0.1 and port 80. 127.0.0.1 is called  loopback address as it tells the browser to look in the consumer’s computer only instead of searching web.

Lets start, first of all socket is a connection point like your phone. You connect to some socket (someone on other side of phone) and send and receive message or data (chat). 
Requires: Python 2.6 + Using: Lets try to create a server first.

#!/usr/bin/python

# Import all from module socket
from socket import *

# Defining server address and port
host = ''  #'localhost' or '127.0.0.1' or '' are all same
port = 52000 #Use port &gt; 1024, below it all are reserved

#Creating socket object
sock = socket()
#Binding socket to a address. bind() takes tuple of host and port.
sock.bind((host, port))
#Listening at the address
sock.listen(5) #5 denotes the number of clients can queue

#Accepting incoming connections
conn, addr = sock.accept()

#Sending message to connected client
conn.send('Hi! I am server') #send only takes string
#Receiving from client
data = conn.recv(1024) # 1024 stands for bytes of data to be received
print data

#Closing connections
conn.close()
sock.close()
Now a client.
#!usr/bin/python
from socket import *

host = 'localhost' # '127.0.0.1' can also be used
port = 52000

sock = socket()
#Connecting to socket
sock.connect((host, port)) #connect takes tuple of host and port

data = sock.recv(1024)
print data
sock.send('HI! I am client.')

sock.close()
Understanding:
Everything is explained in comments.
Note:
  • The program above is waiting for type of programming. Look at the send and recv part in both programming, if server is sending something client must always know when server will send and vice versa or it should wait for that. Of course if you don’t want to drop anything you are sending.
  • # is used for comments except the first line it is for telling where python program is. Use path to python.exe on Windows.
  • Always use sock.close() to close the socket otherwise socket is in use error will be thrown by python. You may also see it if the program terminates in between.
  • 5 in sock.listen is of no use right now as the server.py will terminate as soon as it is done with first client.
  • sock.recv() waits till it does not receive something.
  • print data will not work with python 3.0+. Use print(data).
There will be more on it. Till if you want to read more about it, see here.

Creating custom HTML tags

Why not have something like facebook login button, which we see on many sites now. This is basically custom tag which facebook has made. In this post we will see how it can be made.

Requires: HTML, Javascript, CSS

Using:

CSS for the custom tag

&lt;style type=&quot;text/css&quot;&gt;
@media all 
{
IW\:button {
 font-size: 24pt;
 background-color:#999;
 border-top:1px solid #CCC;
 border-left:1px solid #CCC;
 border-right:2px solid black;
 border-bottom:2px solid black;
 border-radius:5px;
 font-family:&quot;Times New Roman&quot;, Times, serif;
 cursor:pointer; 
 color:black;
 padding:1px;
 padding-left:2px;
 padding-right:2px;
}
} 
span{
 color:white;
 padding-left:2px;
 padding-right:4px;
 border-right: 2px solid #020;
}
&lt;/style&gt;

Javascript for the custom tag

&lt;script type=&quot;text/javascript&quot;&gt;// &lt;![CDATA[
function ActasMytag(myTag)
{
myTag.style.borderLeft = '1px solid #CCC';
myTag.style.borderTop = '1px solid #CCC';
myTag.style.borderRight = '2px solid black';
myTag.style.borderBottom = '2px solid black';
}

function ActasButton(myTag)
{
myTag.style.borderLeft = '1px solid black';
myTag.style.borderTop = '1px solid black';
myTag.style.borderRight = '2px solid #CCC';
myTag.style.borderBottom = '2px solid #CCC';
}
&lt;/script&gt;

HTML to be written for the custom tag

Add ‘IW’ in xmlns of HTML.

&lt;IW:button onmousedown=&quot;ActasButton(this)&quot; onmouseup=&quot;ActasMytag(this)&quot;&gt;IW&amp;nbsp;Custom Button&lt;/IW:button&gt;

Explanation:

Lets start with the HTML first.

Adding IW is an not that important. But should be done as browser can ignore the custom tag as it is not known to it.

Look at the html tag we have created. ‘IW’ is tag name we want to create. Notice the specified ‘button’ after ‘:’, it is written because only one custom tag can be inserted in a page but can have any number of attributes. So, to solve that problem we distinguish different tags on the basis of value written after ‘:’. Next we are calling Javascript function to give this tag functionality of a button.

Javascript

Line 2: This function changes the property back to default property we have set for our custom tag. It is called on mouseup.

Line 10: This function simulates the pressing of button. It is called on mousedown.

CSS

Line 2: @media all, what it does is the xmlns IW will work across the page and will apply CSS specified inside it.

Line 4: IW\:button selects the IW tag with attribute button and apply the defined CSS to it.

Note:

  • @media all is important part otherwise CSS may not work.
  • Modern browsers may run without xmlns and @media all.
  • <IW:button> should be there otherwise if IW is only specified, only one custom tag can be used.
  • JS function ActasButton is important because it makes button looks like its pressed on mouse button down.
  • Functionality like redirecting can be given onclick or performing any other action.

You can download the working repository from github.

Tag hinting or label hinting javascript library

Tag hinting or label hinting is a very useful thing for forums and blogs, where it is used to tag post or as generally termed as auto suggestion.
tag-hinting.js is a Javascript library for embedding tah hinting to a webpage, it is very useful on blogs and forums where you tag post. I have created it in such a way that it is easy to use and understand. All you have to do is include the tag-hinting.js file in your webpage and write the following lines:
&lt;script type=&quot;text/javascript&quot;&gt;
arr = ['your','tag','values'];
&lt;/script&gt;

and a div to show the hints.

&lt;div id=&quot;hintwords&quot; style=&quot;display: none;&quot;&gt;&lt;/div&gt;

The function which is to be called is:

hint(this, event)

Note:

  • Other style values can be added to above div.
  • id of the div has to be hintwords.
  • arr has to be declared and should be after file inclusion.
  • hint take only the specified arguments.
  • hint can work on keypress, keyup and keydown events.

There is a example file on this in repository on git. Pretty soon i’ll be sharing a library which interacts with database and can be helpful in searches.

Javascript Library for dynamic element creation

d-element.js is a javascript library I have created. With the help of it you can easily create dynamic elements, in just one line. Try it!

Required: d-element.js

Using:

$('div').addelement('p',{'background-color':'blue'},'hi');

Explanation:

     This has identical syntax as of jQuery. ‘div’ here is selector which selects all the div tags.
     addelement method takes three arguments of which first is required and null can be given in other two.

Note:

  •     Only element(ex: div), id(ex: .id), class(ex: #class) can be used as selector for now, element under which element has to be created.
  •     First argument of addelement is required which is the element to be created.
  •     Second argument if given should be associative array, it is for the attributes of the element to be created.
  •     Third argument is data, if any string to be inserted. It can be a string having other html elements.
  •     Check console for error logs.