Archive for category mysql
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.
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” »
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.
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.
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” »
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.
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:
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:
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:
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.