Mysql is an open-source relational database management system (RDBMS). It is used widely and runs on the server. MySQL is fast and easy to use. MySQL uses standard SQL and compiles on a number of platforms. It is multithreaded, multi-user SQL database management system.
MYSQL uses a form table to store the data. A table is a collection of related data, and it consists of columns and rows.
Common MySQL Interview Questions.
1. How to improve the performance of SQL Queries?
- Do not use * in the select statement
- Apply Index On Necessary Columns
- Apply FULL-TEXT search
2. Advantages and Disadvantages of Indexes in SQL Server?
While it (mostly) speeds up a select, it slows down inserts, updates and deletes because the database engine does not have to write the data only, but the index, too. An index needs space on the hard disk (and much more important) in RAM. An index that can not be held in RAM is pretty useless. An index on a column with only a few different values doesn’t speed up selects, because it can not sort out many rows (for example a column “gender”, which usually has only two different values – male, and female).
If you use MySQL for example you can check, if the engine uses an index by adding “explain” before the select – for your above example EXPLAIN SELECT TestField FROM Example WHERE username=XXXX
3. When should we use transaction statements?
Use transactions when the set of database operations you are making needs to be atomic.
That is – they all need to succeed or fail. Nothing in between.
Transactions are to be used to ensure that the database is always in a consistent state.
4. Difference between Where and Having Clause in SQL
| WHERE Clause implements in row operations | HAVING Clause implements in column operation |
| WHERE Clause cannot contain aggregate function | HAVING Clause can contain aggregate function |
| WHERE Clause can be used with SELECT, UPDATE, DELETE statements. | HAVING Clause can only be used with SELECT statement. |
| WHERE Clause is used before GROUP BY Clause | HAVING Clause is used after GROUP BY Clause |
| WHERE Clause is used with single row function like UPPER, LOWER etc. | HAVING Clause is used with multiple row function like SUM, COUNT etc. |
5. The differences between UNION ALL and UNION
The major difference between UNION and UNION ALL is that UNION returns distinct records while UNION ALL returns all records including duplicates. While we apply UNION on that time function first apply sort and then find the distinct record and then return a result that’s why it takes more time to compare to UNION ALL. As if we require only distinct records at that time we can use UNION.
Conclusion
MySQL is offered under two different editions: the open-source MySQL Community Server and the proprietary Enterprise Server.
Given these considerations, MySQL has many attractive qualities:
- Speed
- Ease of use
- Query language support
- Capability
- Connectivity and security
- Portability
- Availability and cost
- Open distribution and source code