MySQL Interview Questions Database

Before executing any update/delete on the table data, it’s always best practice to check the number of rows that are going to be affected i.e.,  updated / removed from table with COUNT() operation with the given WHERE condition to cross check whether we’re doing correct update or not

SELECT COUNT(user_id) FROM users WHERE <>;  

will only return the number of user_id s going to effect.

Use DISTINCT in the query, such as

SELECT DISTINCT fname FROM users;

You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT fname) FROM users;

To do that we can use the concatenate function as shown in the below

CONCAT (string1, string2, string3) .

Ex., SELECT CONCAT(fname, “ ”, lname) from users;

When you run mysqladmin status, you'll see something like this:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

This can be somewhat perplexing if you only have 6 tables.

MySQL is multithreaded, so it may have many queries on the same table simultaneously.

To minimize the problem with two threads having different states on the same file, the table is opened independently by each concurrent thread.

This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.

A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network.

To add, access, and process data stored in a computer database, you need a database management system such as MySQL.

Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.

MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users.

You can find a performance comparison of MySQL to some other database managers

on our benchmark page. See section 12.7 Using Your Own Benchmarks.

MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years.

Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.

In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:

If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.

If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.

When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.

In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's.

As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.

To delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the no of indexes. To delete records much faster, we can increase the size of the index cache.

• MyISAM : This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.
• ISAM : same
• HEAP : Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields
• BDB : Supports Transactions using COMMIT & ROLLBACK. Slower that others.
• InoDB : same as BDB

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.

By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.

SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNurrber id, firstname name
FROM employees

id name
103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen

Any RDBMS Transaction is a logical unit of work requested by a user to be applied on the database objects. MySQL server introduces the transaction concept to allow users to group one or many SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

mysql> DELIMITER //
mysql> CREATE PROCEDURE
OfficesByCounties (IN countryName
VARCHAR(255))
-> BEGIN
-> SELECT *
-> FROM offices
-> WHERE country = countryName;
-> END //
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER ;
mysql> create table offices (name
varchar(10), country varchar(50));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into offices values
('here'
,
'india'),('mongo'
,
'usa'),
('citi'
,
'canada'),('JPM'
,
'India');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CALL
GetOfficeByCountry('India');
+------+---------+
| name | country |
+------+---------+
| here | india |
| JPM | India |
+------+---------+
2 rows in set (0.00 sec)

• Written in C and C++.
• Tested with a broad range of different compilers.
• Works on many different platforms.
• Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available
• Provides transactional and nontransactional storage engines.
• Implements in-memory hash tables, which are used as temporary tables.
• Works in cross platform.

When a new threads connects to mysqld, mysqld will span a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.

If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.

You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.

You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking.

SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.

There is no user without a password.
There is no user without a user name.

There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet).

There are as few users as possible (in the ideal case only root) who have unrestricted access.

In MyISAM static all the fields have fixed width.

The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths.

MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or

BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support

AUTO_INCREMENT. Indexes must be NOT NULL.

DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.

If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.

Before MySQL don’t a supported stored procedure, functions, triggers, views, subqueries and partitioning.

But now they are supported. Small and middle level companies are relied on MySQL because it is opensource.

Supports replication and clustering for High Availability.