Archive for category mysql

Batch Insert/Update for Mysql in Java

Large inserts/Updates are a costly operation. One foolish way that I used to do is to run each update or insert query one at a time. Its incredibly slow. With most of the systems currently the latency is what is killing the performance not the CPU. Hence latency per query is really really slow. One way to speed it up is to use the Batch query in Java. It gives you the advantages of prepared statements and more over sends batch of query to mysql and there by making it incredibly fast. How fast? One by one query insert takes around 3 hours for a 8 million inserts, while batch inserts with a batch of 1000 gets done in less than 5mins. Here is the a sample code to it.

Continue reading “Batch Insert/Update for Mysql in Java” »


, , , , ,

No Comments

Important MySQL DBA Interview Questions

Here are a few sets of interview questions expected for MySQL DBA position.These are compilation of the usual questions from various DBA texts: Continue reading “Important MySQL DBA Interview Questions” »


, ,

58 Comments

Get the nth Word from a text field in mysql

I had a requirement to get the nth word out of a string and sort or order by the same. This is used when the text is stored in a purticular format and you would want to order it according to that or group it or so on.

Continue reading “Get the nth Word from a text field in mysql” »


, , , , ,

3 Comments

Display row number in Mysql Select Query

How many times did want to have the row number or the position of the row in the selected query? I had many cases where I needed this feature. I needed the feature for printing the rank of a person where each row is the mark of the person. So lets see how to implement this in mysql.
There is no straight forward function or query parameter to do it (At least none I know of). So I am writing the query to declare a variable and increment that variable on printing that variable along with printing each row. So it just displays the variable on each row. So here is sql query for it. Try it out and tell me.

Continue reading “Display row number in Mysql Select Query” »


, , , , ,

1 Comment

Create a table like another table or from a select query

There are some cases where we need to create a table similar or exactly same as another table. The easiest and best way is use create table along with additional parameters. We can create a table similar to another table without data and with data. Continue reading “Create a table like another table or from a select query” »


, , , , , , , ,

No Comments

Customizing Mysql Prompt For multiple Servers

If you are using mysql regularly on multiple servers its really useful to have the promt in mysql to show something more than mysql>. It would be useful if it showed the username,hostname, current database and things like that.
So how to do this.

Continue reading “Customizing Mysql Prompt For multiple Servers” »


, , , , , , , , , , ,

2 Comments

Generate Date from a range in Mysql and pad the results where no date is present

If you want to generate dates, MYSQL doesn’t have a range function, so I wrote a work around for the same. So how  it works is I have reused a code to generate numbers from my previous post. So I added date function to that query and reduce the generated numbers from the date. Hence effectively its Currrent Date – x number of days. Thus we generate the dates from the date we provide to the x number we generate. So here is the code:

Continue reading “Generate Date from a range in Mysql and pad the results where no date is present” »


, , , , , , , ,

No Comments

Generate 1 to 1000 in mysql

Sometimes its necessary to have thousand rows to join or generate new values, we need to get the thousand rows first. For this I used union of numbers 0-9.  I have joined four of these to get to four digit values. So here is the code:

Continue reading “Generate 1 to 1000 in mysql” »


, , , , , , , ,

2 Comments

Check the port in which the Mysql is running on

Mysql can be configured to run other ports other than the default port (of 3306). So what will you do when you need to find the port on which a mysql server runs on. If you don’t have access to the server files and can access the server only using the mysql client, the only way to know this is by checking the Global Variable set. We can see all variables by issuing the command:

Continue reading “Check the port in which the Mysql is running on” »


, , , , , , , ,

6 Comments

Group by Date when Stored in Mysql DataBase as Date Time

I have a table called date_count with 2 columns one is a value and another is a date time, say called date_time in mysql. Date Time in mysql is stored as dd-mm-yy hh:mm:ss. The issue comes when I need to get the number of rows per day. As its stored as date time format in mysql , you need to specify both date and time when you specify a group by. So grouping by day is not possible in that case. So we need to use the in-build mysql function DATE(). The function accepts date time as parameter and returns only date back. So we just need to use the DATE() function where we need to group by date and all the date time values are converted to date and can be grouped by date. So here is the implementation of it.

Continue reading “Group by Date when Stored in Mysql DataBase as Date Time” »


, , , , , , ,

2 Comments