- Explain MySQL architecture. - The front layer takes care of network connections and security authentications, the middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL (MyISAM) or a commercial one supplied by a third-party vendor (ScaleDB, InnoDB, etc.)
- Explain MySQL locks. - Table-level locks allow the user to lock the entire table, page-level locks allow locking of certain portions of the tables (those portions are referred to as tables), row-level locks are the most granular and allow locking of specific rows.
- Explain multi-version concurrency control in MySQL. - Each row has two additional columns associated with it - creation time and deletion time, but instead of storing timestamps, MySQL stores version numbers.
- What are MySQL transactions? - A set of instructions/queries that should be executed or rolled back as a single atomic unit.
- What’s ACID? - Automicity - transactions are atomic and should be treated as one in case of rollback. Consistency - the database should be in consistent state between multiple states in transaction. Isolation - no other queries can access the data modified by a running transaction. Durability - system crashes should not lose the data.
- Which storage engines support transactions in MySQL? - Berkeley DB and InnoDB.
- How do you convert to a different table type? - ALTER TABLE customers TYPE = InnoDB
- How do you index just the first four bytes of the column? - ALTER TABLE customers ADD INDEX (business_name(4))
- What’s the difference between PRIMARY KEY and UNIQUE in MyISAM? - PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.
- How do you prevent MySQL from caching a query? - SELECT SQL_NO_CACHE …
- What’s the difference between query_cache_type 1 and 2? - The second one is on-demand and can be retrieved via SELECT SQL_CACHE … If you’re worried about the SQL portability to other servers, you can use SELECT /* SQL_CACHE */ id FROM … - MySQL will interpret the code inside comments, while other servers will ignore it.
- Which will be faster out of these two queries - one with OR or one with IN?
- Where does MyISAM cache table records?
- Which will be faster out of queries with explicit INNER JOIN and implicit one?
- Is InnoDB faster/better than MyISAM?
- Is CHAR faster than VARCHAR?
- Is VARCHAR(80) faster than VARCHAR(255)?
- Are there performance issues when joining tables from different storage engines?
- If I change a derived table to a view, will performance increase?
- If I see Using temporary; Using filesort” in the Extra column of EXPLAIN output, does that mean a temporary table is created on disk?
- Is it possible to do a FULL OUTER JOIN in MySQL?
- How do you show the currently running queries? - SHOW FULL PROCESSLIST;
- How do you kill a MySQL query? - See the ID of it from the question above, then KILL id. You can separate multiple IDs by space.
- I need to find out how many client connections were aborted by MySQL server. - It’s displayed in SHOW STATUS query, alternatively accessible via mysqladmin extended-status.
- What does SET SQL_AUTO_IS_NULL =1 do? - you can find the last inserted row for a table that contains an AUTO_INCREMENT column by issuing WHERE auto_increment_column IS NULL
- Why is a UNION ALL faster than a UNION? The union operation, you will recall, brings two sets of data together. It will *NOT* however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.
- How do you return the top-N results of a query in
Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;
MYSQL Advanced
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment