eduguru 
Easiest way to do this is to add a UNIQUE index on the column. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error … Read more
Applications that access MySQL should not trust any data entered by users, who can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like “; DROP DATABASE mysql;”. This is … Read more
This article describes general security issues to be aware of and what you can do to make your MySQL installation more secure against attack or misuse. Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes. In discussing security, it is necessary to consider fully … Read more
The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified. There are two potential security issues with supporting the LOCAL version of LOAD DATA statements: The transfer of the file from … Read more
Here is an example to select multiple column of a table from select statement in asterisk. exten => h,1,MYSQL(Connect conn localhost username password database) exten => h,n,MYSQL(Query res ${conn} ‘SELECT call_id,callerid,calltime FROM todaycall WHERE calltime=curdate() order by calltime desc limit 1′) exten => h,n,MYSQL(Fetch fid ${r} call_id callerid calltime) exten => h,n,MYSQL(Clear ${r}) exten => … Read more
Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave periodically, daily, or even more frequently. Checking Replication Status: The most common task when managing a replication process is to ensure that replication is taking place … Read more
Apachetop is very simple command line utility that you can use to monitor traffic real-time. It accomplishes this by parsing the apache logfiles and displaying meaningful output to the screen. How to install Apachetop yum -y install apachetop How this used to display the http request # apachetop -r 5 -T 30 This means apachetop will … Read more
In this post we will see how can we optimize where clause. Where clause restrict the result set to a range. Basically this is used to access the range of a table/view. The range access method uses a single index to retrieve a subset of table rows that are contained within one or several index … Read more
Some of the optimizations performed by MySQL on where clauses as follow: Removal of unnecessary parentheses: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) To (a AND b AND c) OR (a AND b AND c AND d) Constant folding: (a<b AND b=c) AND a=5 To b>5 AND b=c AND … Read more
The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines of this post will help to speed up all kinds of MySQL applications. The guidelines cover SQL operations that read and write data, the behind-the-scenes overhead … Read more