Question
1
:
How does SQL Server store the datetime data type?
Answer
1 :
SQL Server uses 8 bytes to store the datetime data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1900. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.
Question
2
:
Access Control, Stage 1: Connection Verification?
Answer
2 :
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether or not you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
The host from which you connect
Your MySQL user name
Identity checking is performed using the three user table scope fields (Host, User, and Password). The server accepts the connection only if a user table entry matches your hostname and user name, and you supply the correct password.
Values in the user table scope fields may be specified as follows:
A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host.
You can use the wild-card characters `%' and `_' in the Host field.
A Host value of '%' matches any hostname. A blank Host value is equivalent to '%'. Note that these values match any host that can create a connection to your server!
As of MySQL Version 3.23, for Host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example:
GRANT ALL PRIVILEGES on db.* to david@'192.58.197.0/255.255.255.0';
This will allow everyone to connect from an IP where the following is true:
user_ip & netmask = host_ip.
In the above example all IP:s in the interval 192.58.197.0 - 192.58.197.255 can connect to the MySQL server.
Wild-card characters are not allowed in the User field, but you can specify a blank value, which matches any name. If the user table entry that matches an incoming connection has a blank user name, the user is considered to be the anonymous user (the user with no name), rather than the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during Stage 2).
The Password field can be blank. This does not mean that any password matches, it means the user must connect without specifying a password.
Non-blank Password values represent encrypted passwords. MySQL does not
Question
3
:
But what if you really want to store the timestamp data, such as the publication date of the article?
Answer
3 :
Create two columns of type TIMESTAMP and use the second one for your real data.
Question
4
:
Can you save your connection settings to a conf file?
Answer
4 :
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
Question
5
:
Causes of Access denied Errors?
Answer
5 :
If you encounter Access denied errors when you try to connect to the MySQL server, the list below indicates some courses of action you can take to correct the problem:
The server should let you connect without error. You should also make sure you have a file `user.MYD' in the MySQL database directory. Ordinarily, this is `PATH/var/mysql/user.MYD', where PATH is the pathname to the MySQL installation root.
After a fresh installation, you should connect to the server and set up your users and their access permissions:
shell> mysql -u root mysql
The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the root password is something you should do while you're setting up your other MySQL users. If you try to connect as root and get this error:
Access denied for user: '@unknown' to database mysql
this means that you don't have an entry in the user table with a User column value of 'root' and that mysqld cannot resolve the hostname for your client. In this case, you must restart the server with the --skip-grant-tables option and edit your `/etc/hosts' or `\windows\hosts' file to add an entry for your host.
If you updated an existing MySQL installation from a version earlier than Version 3.22.11 to Version 3.22.11 or later, did you run the mysql_fix_privilege_tables script? If not, do so. The structure of the grant tables changed with MySQL Version 3.22.11 when the GRANT statement became functional.
If you can't get your password to work, remember that you must use the PASSWORD() function if you set the password with the INSERT, UPDATE, or SET PASSWORD statements. The PASSWORD() function is unnecessary if you specify the password using the GRANT ... INDENTIFIED BY statement or the mysqladmin password command.
localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly. However, connections to localhost do not work if you are running on a system that uses MIT-pthreads (localhost connections are made using Unix sockets, which are not supported by MIT-pthreads). To avoid this problem on such systems, you should use the --host option to name the server host explicitly. This will make a TCP/IP connection to the mysqld serve
Question
6
:
Design Choices ?
Answer
6 :
MySQL keeps row data and index data in separate files. Many (almost all) other databases mix row and index data in the same file. We believe that the MySQL choice is better for a very wide range of modern systems.
Another way to store the row data is to keep the information for each column in a separate area (examples are SDBM and Focus). This will cause a performance hit for every query that accesses more than one column. Because this degenerates so quickly when more than one column is accessed, we believe that this model is not good for general purpose databases.
The more common case is that the index and data are stored together (like in Oracle/Sybase et al). In this case you will find the row information at the leaf page of the index. The good thing with this layout is that it, in many cases, depending on how well the index is cached, saves a disk read. The bad things with this layout are:
Table scanning is much slower because you have to read through the indexes to get at the data.
You can't use only the index table to retrieve data for a query.
You lose a lot of space, as you must duplicate indexes from the nodes (as you can't store the row in the nodes).
Deletes will degenerate the table over time (as indexes in nodes are usually not updated on delete).
It's harder to cache ONLY the index data.
MySQL Design Limitations/Tradeoffs
Because MySQL uses extremely fast table locking (multiple readers / single writers) the biggest remaining problem is a mix of a steady stream of inserts and slow selects on the same table.
We believe that for a huge number of systems the extremely fast performance in other cases make this choice a win. This case is usually also possible to solve by having multiple copies of the table, but it takes more effort and hardware.
We are also working on some extensions to solve this problem for some common application niches.
Question
7
:
Error severity 13 indicates what?
Answer
7 :
Transactional deadlock errors.
This level of error severity indicates a transaction deadlock error.
Question
8
:
Estimating Query Performance ?
Answer
8 :
In most cases you can estimate the performance by counting disk seeks. For small tables, you can usually find the row in 1 disk seek (as the index is probably cached). For bigger tables, you can estimate that (using B++ tree indexes) you will need: log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 seeks to find a row.
In MySQL an index block is usually 1024 bytes and the data pointer is usually 4 bytes. A 500,000 row table with an index length of 3 (medium integer) gives you: log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks.
As the above index would require about 500,000 * 7 * 3/2 = 5.2M, (assuming that the index buffers are filled to 2/3, which is typical) you will probably have much of the index in memory and you will probably only need 1-2 calls to read data from the OS to find the row.
For writes, however, you will need 4 seek requests (as above) to find where to place the new index and normally 2 seeks to update the index and write the row.
Note that the above doesn't mean that your application will slowly degenerate by N log N! As long as everything is cached by the OS or SQL server things will only go marginally slower while the table gets bigger. After the data gets too big to be cached, things will start to go much slower until your applications is only bound by disk-seeks (which increase by N log N). To avoid this, increase the index cache as the data grows.
Question
9
:
Explain advantages of MyISAM over InnoDB?
Answer
9 :
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
Question
10
:
Explain federated tables. ?
Answer
10 :
Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.