Posts

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: What is Log Switch? – The point at which ORACLE ends writing to one online redo log file and begins writing to …

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. One m …

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 mysq …

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 dat …

Check if a table exists in MySQL from PHP

Sometimes its necessary to execute a query or execute some code if a table exists or not. So a bit of searching got me to the conclusion that mysql doesn’t have a direct query or method for it from php. So there are two methods to do it. Use the mysql c …

Ontology Linked Data

The following is a Assignment on Ontology and Linked Data and the evolution of data which lead to the use of ontology in web. Thanks to my teammates Bharath Gowda and Sravani Thota for working on the same. Ontology And Linked Data An ontology formally r …

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. So how is it done? We do it using a  …

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 l …

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  …

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  …