My SQL Interview Questions and Answers

Answer - 81 : - If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.

Answer - 82 : - You should definately benchmark your application and database to find out where the bottlenecks are. By fixing it (or by replacing the bottleneck with a 'dummy module') you can then easily identify the next bottleneck (and so on). Even if the overall performance for your application is sufficient, you should at least make a plan for each bottleneck, and decide how to solve it if someday you really need the extra performance. For an example of portable benchmark programs, look at the MySQL benchmark suite. You can take any program from this suite and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you. It is very common that some problems only occur when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In every one of these cases so far, it has been problems with basic design (table scans are NOT good at high load) or OS/Library issues. Most of this would be a LOT easier to fix if the systems were not already in production. To avoid problems like this, you should put some effort into benchmarking your whole application under the worst possible load! You can use Sasha's recent hack for this - super-smack. As the name suggests, it can bring your system down to its knees if you ask it, so make sure to use it only on your development systems.

Answer - 83 : - Because all SQL servers implement different parts of SQL, it takes work to write portable SQL applications. For very simple selects/inserts it is very easy, but the more you need the harder it gets. If you want an application that is fast with many databases it becomes even harder! To make a complex application portable you need to choose a number of SQL servers that it should work with. You can use the MySQL crash-me program/web-page http://www.mysql.com/information/crash-me.php to find functions, types, and limits you can use with a selection of database servers. Crash-me now tests far from everything possible, but it is still comprehensive with about 450 things tested. For example, you shouldn't have column names longer than 18 characters if you want to be able to use Informix or DB2. Both the MySQL benchmarks and crash-me programs are very database-independent. By taking a look at how we have handled this, you can get a feeling for what you have to do to write your application database-independent. The benchmarks themselves can be found in the `sql-bench' directory in the MySQL source distribution. They are written in Perl with DBI database interface (which solves the access part of the problem). See http://www.mysql.com/information/benchmarks.html for the results from this benchmark. As you can see in these results, all databases have some weak points. That is, they have different design compromises that lead to different behavior. If you strive for database independence, you need to get a good feeling for each SQL server's bottlenecks. MySQL is VERY fast in retrieving and updating things, but will have a problem in mixing slow readers/writers on the same table. Oracle, on the other hand, has a big problem when you try to access rows that you have recently updated (until they are flushed to disk). Transaction databases in general are not very good at generating summary tables from log tables, as in this case row locking is almost useless. To get your application really database-independent, you need to define an easy extendable interface through which you manipulate your data. As C++ is available on most systems, it makes sense to use a C++ classes interface to t

Answer - 84 : - During MySQL initial development, the features of MySQL were made to fit our largest customer. They handle data warehousing for a couple of the biggest retailers in Sweden. From all stores, we get weekly summaries of all bonus card transactions, and we are expected to provide useful information for the store owners to help them find how their advertisement campaigns are affecting their customers. The data is quite huge (about 7 million summary transactions per month), and we have data for 4-10 years that we need to present to the users. We got weekly requests from the customers that they want to get 'instant' access to new reports from this data. We solved this by storing all information per month in compressed 'transaction' tables. We have a set of simple macros (script) that generates summary tables grouped by different criteria (product group, customer id, store ...) from the transaction tables. The reports are Web pages that are dynamically generated by a small Perl script that parses a Web page, executes the SQL statements in it, and inserts the results. We would have used PHP or mod_perl instead but they were not available at that time. For graphical data we wrote a simple tool in C that can produce GIFs based on the result of a SQL query (with some processing of the result). This is also dynamically executed from the Perl script that parses the HTML files. In most cases a new report can simply be done by copying an existing script and modifying the SQL query in it. In some cases, we will need to add more fields to an existing summary table or generate a new one, but this is also quite simple, as we keep all transactions tables on disk. (Currently we have at least 50G of transactions tables and 200G of other customer data.) We also let our customers access the summary tables directly with ODBC so that the advanced users can themselves experiment with the data. We haven't had any problems handling this with quite modest Sun Ultra SPARCstation (2x200 Mhz). We recently upgraded one of our servers to a 2 CPU 400 Mhz UltraSPARC, and we are now planning to start handling transactions on the product level, which would mean a ten-fold increase of data. We think we can keep up with this by just adding mo

Answer - 85 : - mysql_fetch_array — Fetch a result row as an associative ARRAY, a numeric array, or both mysql_fetch_object — Fetch a result row as an OBJECT

Answer - 86 : - The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”).

Answer - 87 : - 1) Internals and Portability       Written in C and C++, Works on different platforms, Fully multi-threaded, SQL functions are implemented 2) Column Types       signed/unsigned integers, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM, and OpenGIS spatial types 3) Statements and Functions       Full operator and function support in the SELECT and WHERE clauses of queries       Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT ...), AVG(), STD(), SUM(), MAX(), MIN(), and GROUP_CONCAT()).       Support for LEFT OUTER JOIN and RIGHT OUTER JOIN       Support for aliases on tables and columns as required by standard SQL       DELETE, INSERT, REPLACE, and UPDATE return the number of rows that were changed (affected)       SHOW command can be used to retrieve information about databases, database engines, tables, and indexes       EXPLAIN command can be used to determine how the optimizer resolves a query.       Function names do not clash with table or column names 4) Security       A privilege and password system that is very flexible and secure, because all password traffic is encrypted 5) Scalability and Limits       Databases can contain 50 million records, 60,000 tables and about 5,000,000,000 rows       64 indexes per table are allowed and Each index may consist of 1 to 16 columns or parts of columns, maximum index width is 1000 bytes (CHAR, VARCHAR, BLOB, or TEXT ) 6) Connectivity       Clients can connect to the MySQL server using TCP/IP sockets on any platform       Windows systems in the NT family (NT, 2000, XP, or 2003)       On Unix systems, clients can connect using Unix domain socket files. 7) Localization  &nb

Answer - 88 : -

Answer - 89 : -

Answer - 90 : -