View is a virtual table. The fields in a view are fields from one or more real tables in the database.
Advantages and Disadvantages of indexes
Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like primary key index, unique index, normal index also known as ("non-unique index", ordinary index, index without constraints) and full-text index. Of course, the indexes improve SELECT queries speed tremendously. but, they do have some considerable disadvantages as well. Advantages of MySQL Indexes
Disadvantages of MySQL indexes
When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you are interested in sorting on.
Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.
Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.
What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.
• BLOB or TEXT fields are not allowed
• Only comparison operators can be used =, <,>, = >,=<
• AUTO_INCREMENT is not supported by HEAP tables
• Indexes should be NOT NULL
How do you login to MySql using Unix shell?
We can login through this command:
# [mysql dir]/bin> mysql -h 127.0.0.1 -u root -p
How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL
How do you start MySQL on Linux? - /etc/init.d/mysql start OR sudo service mysql start
How do you change a password for an existing user via mysqladmin? - mysqladmin -u root -p password "newpassword"
What does tee command do in MySQL?
Tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
MYISAM: Vs INNODB:
- MYISAM supports Table-level Locking
- MyISAM designed for need of speed
- MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
- MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
- MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
- MYISAM supports fulltext search
- You can use MyISAM, if the table is more static with lots of select and less update and delete.
- InnoDB supports Row-level Locking
- InnoDB designed for maximum performance when processing high volume of data
- InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
- InnoDB stores its tables and indexes in a tablespace
- InnoDB supports transaction. You can commit and rollback with InnoDB
Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.
Foreign Key – Foreign key are those keys which is used to define relationship between two tables. When we want to implement relationship between two tables then we use concept of foreign key. It is also known as referential integrity. We can create more than one foreign key per table. foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.
Alternate Key – If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table. Like here we can take a very simple example to understand the concept of alternate key. Suppose we have a table named Employee which has two columns EmpID and EmpMail, both have not null attributes and unique value. So both columns are treated as candidate key. Now we make EmpID as a primary key to that table then EmpMail is known as alternate key.
Composite Key – When we create keys on more than one column then that key is known as composite key. Like here we can take an example to understand this feature. I have a table Student which has two columns Sid and SrefNo and we make primary key on these two column. Then this key is known as composite key.
MyISAM Static and MyISAM Dynamic
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.
HAVING clause - The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
MySQL Complex Queries
1. Delete all duplicate rows except last inserted row.
mysql > DELETE t1 FROM user t1, user t2 WHERE t1.id < t2.id AND t1.email = t2.email;
2. Find max and second max salary for a employee table MySQL.
mysql > SELECT
(SELECT MAX(Salary) FROM Employee) maxsalary,
(SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )) as 2nd_max_salary;
3. Fetch the single record who has duplicate records in the table
mysql > SELECT * FROM `user` having count(email) > 1;
4. Fetch all record who has duplicate records in the table
mysql > SELECT * FROM `user` where email in (select email from user having count(email) > 1 );
5 Select 10 rows from the decreasing passed id and 10 rows increasing passed id
mysql > SELECT * FROM `bm_customers` WHERE pk >= ( 787702 -10 ) AND pk <= ( 787702 +10 ) ORDER BY `pk` DESC LIMIT 0 , 30