Technical Interview Questions
Oracle Interview Questions
MsSql Interview Questions
My SQL Interview Questions and Answers
How MySQL uses DNS ?
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
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.
MySQL - Get Your Data as Small as Possible
One of the most basic optimization is to get your data
(and indexes) to take as little space on the disk (and
in memory) as possible. This can give huge improvements
because disk reads are faster and normally less main
memory will be used. Indexing also takes less resources
if done on smaller columns.
MySQL supports a lot of different table types and row
formats. Choosing the right table format may give you a
big performance gain.
You can get better performance on a table and minimize
storage space using the techniques listed below:
Use the most efficient (smallest) types possible. MySQL
has many specialized types that save disk space and
Use the smaller integer types if possible to get smaller
tables. For example, MEDIUMINT is often better than INT.
Declare columns to be NOT NULL if possible. It makes
everything faster and you save one bit per column. Note
that if you really need NULL in your application you
should definitely use it. Just avoid having it on all
columns by default.
If you don't have any variable-length columns (VARCHAR,
TEXT, or BLOB columns), a fixed-size record format is
used. This is faster but unfortunately may waste some
The primary index of a table should be as short as
possible. This makes identification of one row easy and
efficient. For each table, you have to decide which
storage/index method to use.
Only create the indexes that you really need. Indexes
are good for retrieval but bad when you need to store
things fast. If you mostly access a table by searching
on a combination of columns, make an index on them. The
first index part should be the most used column. If you
are ALWAYS using many columns, you should use the column
with more duplicates first to get better compression of
If it's very likely that a column has a unique prefix on
the first number of characters, it's better to only
index this prefix. MySQL supports an index on a part of
a character column. Shorter indexes are faster not only
because they take less disk space but also because they
will give you more hits in the index cache and thus
fewer disk seeks.
In some circumstances it can be beneficial to split into
two a table that is scanned very often. This is
especially true if it is a dynamic format table and it
is possible to use a smaller static format table that
can be used to find the relevant rows when scanning the
Page Numbers : 1