Priti's Technical Corner

November 13, 2008

Mysql Questions

Filed under: Mysql — Tags: , , — pritisolanki @ 5:38 pm

Question 1: Name different types of storage engines you worked with?
Answer 1: Innodb, ISAM (I worked with them).

Question 2: What are the main features of Mysql 5?
Answer 2: In mysql 5+ we are provided with following functionalities

1. Event Scheduler is introduce to set up events like we do in crontab on Linux
2. Server log tables can be written in to log files or general_log and slow_log tables.
3. Introduced ‘mysql_upgrade’ program which checks all existing tables for incompatibilities with the current version of mysql and repair if necessary.
3. The mysqldump utility now supports an option for dumping tablespaces
4. MySQL 5.1 provides much more information in its metadata database o.e New tables in the INFORMATION_SCHEMA database include FILES, EVENTS, PARTITIONS, PROCESSLIST, ENGINES, and PLUGINS.
5. The mysqlslap program is designed to emulate client load for a MySQL server and report the timing of each stage. It works as if multiple clients were accessing the server

Question 3: What is right join?
Answer 3: All the records from right table + matching records from left table.

Question 4: Can we delete rows based on joins in mysql?
Answer 4: Yes

Question 5: How to take backup of your mysql database?

Answer 5: mysqldump on linux OR you can use PHPMyAdmin interface to export the Database.

Question 6:How to load text file in to table.
Answer 6:LOAD DATA LOCAL INFILE ‘/path/filename.txt’ INTO TABLE table_name;

Question 7: what is difference between now( ) , curdate()?
Answer 7:curdate() return date in “YYYY-MM-DD” format.
now() return the current timestamp in “YYYY-MM-DD HH:MM:SS” format.

Question 8: Write query to matches only lowercase ‘a’ at the beginning of a name
Answer  8: SELECT * FROM pet WHERE name REGEXP BINARY ‘^a’;

Advertisements

3 Comments »

  1. playing with limit and offset in mysql/php

    Use LIMIT in your sql query like this:

    SELECT name FROM table LIMIT 10 // will return 10 rows

    if you want to get the rows between 10 and 20 do the following:

    SELECT name FROM table LIMIT 9, 10 // row starts from 0

    This statement retrieves all rows from the 96th row to the last:

    SELECT * FROM tbl LIMIT 95,18446744073709551615; // use some large number for the second parameter.

    MySQL using LIMIT that take two arguments. The first argument specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the first row is 0 ( not 1 ).

    In php you can use it for pagination:

    // how many rows to show per page
    $rowsPerPage = 20;

    // by default we show first page
    $pageNum = 1;

    // if $_GET[‘page’] defined, use it as page number
    if(isset($_GET[‘page’]))
    {
    $pageNum = $_GET[‘page’];
    }

    // counting the offset
    $offset = ($pageNum – 1) * $rowsPerPage;

    $query = ” SELECT val FROM randoms ” .
    ” LIMIT $offset, $rowsPerPage”;
    $result = mysql_query($query) or die(’Error, query failed’);

    Comment by Anant Singh — December 16, 2008 @ 11:43 am

  2. Mysql backups will fit into two types of backups: a dump or raw backup.

    A MySQL dump is a bit slower than a raw backup because it creates all the SQL queries required to create the tables of that database, as well as all the insert queries required to place the information back into the database’s tables.

    A MySQL Raw Backup is quicker because it does not translate the contents of the database into human readable SQL queries

    Comment by Anant Singh — December 16, 2008 @ 4:45 pm

  3. what are overheads?

    Take a varchar. Assume you add three items with that field set to some string of a certain length. Then you change the first one’s field to be shorter. To save disk cost, the database probably won’t pull the other fields back to fill in the newly freed space. That’s overhead. Optimizing will do such compression

    Every database will, over time, require some form of maintenance to keep it at an *optimal* performance level. Purging deleted rows, resequencing, compressing, managing index paths, defragmenting, etc. is what is known as OPTIMIZATION in MySQL and other terms in other databases. For example, IBM DB2/400 calls it REORGANIZE PHYSICAL FILE MEMBER.

    It’s kind of like changing the oil in your car or getting a tune-up. You may think you really don’t have to, but by doing so your car runs much better, you get better gas mileage, etc. A car that gets lots of mileage requires tune-ups more often. A database that gets heavy use requires the same. If you are doing a lot of UPDATE and/or DELETE operations, and especially if your tables have variable length columns (VARCHAR, TEXT, etc), you need to keep ‘er tuned up.

    Comment by Anant Singh — December 16, 2008 @ 5:16 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: