MySQL FAQ

my.cnf

The default engine is InnoDB as of MySQL 5.5.5 and MyISAM before 5.5.5

InnoDB provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity).

3306

May be mysql_pconnect() is used, which will open persistent connection.

Use of AES_ENCRYPT (),AES_DECRYPT () functions in mysql for encryption and decryption used to serve the purpose earlier. However, these reversible encryption functions (like AES_ENCRYPT and ENCODE) are DEPRECATED since their attack as much easier than irreversible functions.

So, one can use irreversible function instead. e.g. SHA1, MD5 etc.

Also, there's a library from php for encryption which is php5_mcrypt. All what you need to install the library on server and just call a php function. However, this feature was DEPRECATED in PHP 7.1.0, and REMOVED in PHP 7.2.0. Alternatives to this feature include: Sodium (available as of PHP 7.2.0) or OpenSSL

mysqldump db_name > db_name.sql

mysql -p -u username dbname < file.sql

Note, file must be in . sql format

MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement.

INNER JOIN (or sometimes called simple join). It is the most common type of join. MySQL INNER JOINS return all rows from multiple tables where the join condition is met. The MySQL INNER JOIN would return the records where table1 and table2 intersect.

Syntax
The syntax for the INNER JOIN in MySQL is:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT OUTER JOIN(or sometimes called LEFT JOIN). This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met). The MySQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Syntax
The syntax for the LEFT OUTER JOIN in MySQL is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2

ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

RIGHT OUTER JOIN(or sometimes called RIGHT JOIN). This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met). The MySQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Syntax
The syntax for the RIGHT OUTER JOIN in MySQL is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Innodb has only .frm files and it has its own tablespace where it contains indexes and datas and its shared in databases.

MyIsam has 3 different files are created on upon creating a new table viz., . frm, . myi and . myd where .myi has your indexes, . myd has your table datas and . frm has its table definition.

Generally you can find the data directory of your file with your . my . cnf file. The physical structure of db is some thing like a directory structure, where each database is a subdirectory under the main directory and has some files in it. Each table has its own file. Bascially one can see three types of files . frm, . myi, . myd for each table. But they are not same for all tables and db. They differ based on the engines you use and sometimes even differ with the os. There are lots of other factors that is in the backend behind the type of files you see.

You can use these files when your db crash, or when you upgrade your db to another version and it can also be used while migrating and repairing your indexes without affecting data.

ENUM

mysql_error()

SHOW DATABASES

slow query log file

limit 23, 100

select * from users where phone_number IS NULL;

SHOW INDEX FROM tablename

select MONTHNAME(DATE(CURRENT_TIMESTAMP))

Generally, UNIX stores 23 bit integer value as timestamp whereas MySQL stores proper formatted value as YYYY-MM-DD HH:MM:SS format.

In binary format using BLOB datatype. A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. These differ only in the maximum length of the values they can hold.

Each of table name, database name and fieldname has 64 chanracters length.

MySQL SET can take zero or more values but at the maximum it can take 64 values.

DESCRIBE and EXPLAIN. They are used either to obtain information about table structure or query execution plans. DESC is a short form of DESCRIBE.

COUNT(*) counts the number of rows in table.

To get the Current Date:

1. SELECT CURDATE();
2. SELECT CURRENT_DATE();

To get the Current Time:

1. SELECT CURTIME();
2. SELECT CURRENT_TIME();

To get both at the same time:

1. SELECT NOW();
2. SELECT SYSDATE();

ORDER BY alters the order in which items are returned. The ORDER BY clause's purpose is to sort the query result by specific columns.

GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc). The GROUP BY clause’s purpose is summarize unique combinations of columns values.

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

Values in VARCHAR columns are variable - length strings. The length can be specified as a value from 0 to 65,535.

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

The LOCAL keyword affects expected location of the file and error handling, as described later. LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with the local_infile system variable disabled, LOCAL does not work.

By using DATEDIFF(expr1,expr2) function. DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE table_name
MODIFY COLUMN column_name column_definition
[FIRST | AFTER column_name];

TRUNCATE

TRUNCATE drops the table and re-create it. It is much faster than deleting rows one by one.
Once TRUNCATE command used, one cannot retrieve the data again from the table.
TRUNCATE is not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.
TRUNCATE removes all the rows from the Table.
TRUNCATE does not return number of deleted rows.
TRUNCATE does not log the individual row deletions.
TRUNCATE functionality is an Oracle SQL extension adopted in MySQL.
TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.
TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

TRUNCATE TABLE works similar to DELETE if there are foreign key constraints that reference the table; otherwise it simply drops and re-creates the table. The AUTO_INCREMENT value is reset by TRUNCATE TABLE even ignoring foreign key constraint, if there is.

DROP TABLE goes further than a TRUNCATE in that it actually removes the table from the database completely. This includes removing all associated objects, such as indexes and constraints. If you dropped a table, not only would you remove all the data, but the structure as well. This will usually be done when a table is no longer needed. The primary concern is that since a DROP is DDL, you typically can't undo it. Some RDBMS engines will allow you to wrap DDL within a transaction so you can roll it back, but this is not considered best practice and should be avoided.

The main considerations for optimizing queries are:

• To make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. Set up indexes on columns used in the WHERE clause, to speed up evaluation, filtering, and the final retrieval of results. To avoid wasted disk space, construct a small set of indexes that speed up many related queries used in your application.
• Isolate and tune any part of the query, such as a function call, that takes excessive time. Depending on how the query is structured, a function could be called once for every row in the result set, or even once for every row in the table, greatly magnifying any inefficiency.
• Minimize the number of full table scans in your queries, particularly for big tables.
• Keep table statistics up to date by using the ANALYZE TABLE statement periodically, so the optimizer has the information needed to construct an efficient execution plan.
• Avoid transforming the query in ways that make it hard to understand, especially if the optimizer does some of the same transformations automatically.
• If a performance issue is not easily solved by one of the basic guidelines, investigate the internal details of the specific query by reading the EXPLAIN plan and adjusting your indexes, WHERE clauses, join clauses, and so on.
• Adjust the size and properties of the memory areas that MySQL uses for caching. With efficient use of the InnoDB buffer pool, MyISAM key cache, and the MySQL query cache, repeated queries run faster because the results are retrieved from memory the second and subsequent times.
• Even for a query that runs fast using the cache memory areas, you might still optimize further so that they require less cache memory, making your application more scalable. Scalability means that your application can handle more simultaneous users, larger requests, and so on without experiencing a big drop in performance.
• Deal with locking issues, where the speed of your query might be affected by other sessions accessing the tables at the same time.
• Because each InnoDB table has a primary key (whether you request one or not), specify a set of primary key columns for each table, columns that are used in the most important and time-critical queries.
• Do not specify too many or too long columns in the primary key, because these column values are duplicated in each secondary index. When an index contains unnecessary data, the I/O to read this data and memory to cache it reduce the performance and scalability of the server.
• Do not create a separate secondary index for each column, because each query can only make use of one index. Indexes on rarely tested columns or columns with only a few different values might not be helpful for any queries. If you have many queries for the same table, testing different combinations of columns, try to create a small number of concatenated indexes rather than a large number of single-column indexes. If an index contains all the columns needed for the result set (known as a covering index), the query might be able to avoid reading the table data at all.
• If an indexed column cannot contain any NULL values, declare it as NOT NULL when you create the table. The optimizer can better determine which index is most effective to use for a query, when it knows whether each column contains NULL values.

You can GRANT and REVOKE privileges on various database objects in MySQL. You can then view the privileges assigned to a user using the SHOW GRANTS command.

GRANT Privileges on Table

Syntax
The syntax for granting privileges on a table in MySQL is:

GRANT privileges ON object TO user;

privileges It can be any of the following values:

object

The name of the database object that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.

user

The name of the user that will be granted these privileges.

Example :
GRANT SELECT, INSERT, UPDATE, DELETE ON contacts TO 'sandip'@'localhost';

REVOKE Privileges on Table

Syntax
The syntax for revoking privileges on a table in MySQL is:

REVOKE privileges ON object FROM user;

privileges It can be any of the following values:

object

The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user

The name of the user that will have these privileges revoked.

Example :
REVOKE DELETE, UPDATE ON contacts FROM 'sandip'@'localhost';

Database normalization, or simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to reduce data redundancy and improve data integrity. Normalization is also the process of simplifying the design of a database so that it achieves the optimal structure composed of atomic elements. It was first proposed by Edgar F. Codd, as an integral part of a relational model.

It's basically, a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

Edgar F. Codd, the inventor of the Relational Model, introduced the concept of normalization and what is now known as the first normal form (1NF) in 1970. He went on to define the second normal form (2NF) and third normal form (3NF) in 1971, and Codd and Raymond F. Boyce defined the Boyce-Codd normal form (BCNF) in 1974.

Stored Procedure: A stored procedure is a set of Sql commands that can be compiled and stored in the server. Once this has been done, client don't need to keep re-issuing the entire query but can refer to the stored procedures. This provide better overall performance because the query has to be passed only once and less information need to be sent between the server and client. Stored procedures of course do increase the load on the database server system, as more of the work is done on server side and less on client (application) side.

Trigger: A trigger is effectively a type of stored procedure, and that is invoke when an particular event occours. For instance, We can install a stored procedure that is triggered each time a record is deleted from a transaction table and that invoke stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

Indexes: Indexes are used to find rows with specific column values quickly. Without an index, mysql must begin with the first row and then read through the entire table to find the relavant rows. The larger the table, the more the cost. If table has an index for the column in question, mysql can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1000 rows, this is the least 100 times faster than reading sequentially.

A full-text index in MySQL is an index of type FULLTEXT. Full-text indexes can be used only with MyISAM tables, arid can be created only for CHAR, VARCHAR or TEXT columns. For large data sets, it is much faster to load your data into a table that has no FULL TEXT index and then create the index after that. e.g. SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP')

Search Types:

• A Boolean Search
• A natural language search (default if not specified any of other two)
• A query expansion search

• MyISAM is the default table type before 5.5.5 and InnoDB as of MySQL 5.5.5 and onwards.
• MyISAM supports table level locking and not row level whereas InnoDB supports row level locking. Means, in case of MyISAM, if the data in one table has been modified by the other table, the entire table will lock for the next process and all further queries gets queued up till the lock released.
• InnoDB hence provides transactions whereas MyISAM does not.
• Foreign Key constraint is not supported in MyISAM whereas InnoDB does support it.
• MyISAM is best fit for small tables whereas InnoDB generally works great with large tables.
• MyISAM is faster than the InnoDB but in case of performing the count operations it take more time than the InnoDB.

Main purpose of HEAP table type is maximum speed for managing relatively small data.

HEAP tables exists only in RAM (not on hard drive). They use a hash index, which results in particularly fast access to individual data records. HEAP tables are often used as temporary tables.

HEAP tables presents a large number of functional restrictions such as no TEXT or BLOB data types, searches records with only "=", no Auto Increment Support etc.

HEAP tables are mainly used whenever relatively small data sets are to be managed with maximum speed.

Since HEAP tables are stored exclusively in RAM, they disappear as soon as MySQL is terminated. The maximum size of a HEAP table is determined in the MySQL configuration file by the parameter max_heap_table_size

Temporary tables are invisible to other MySQL links (so that it is possible for two users to employ temporary tables with the same name without running into trouble).

Temporary tables are not a separate table type unto themselves, but rather a variant of the types that we have been describing. Temporary tables are often created automatically by MySQL in order to assist in the execution of SELECT queries.

Temporary tables are not stored in the same directory as the other MySQL tables, but in a special temporary directory (under Windows it is usually called C : \ Windows \ Temp, while under Unix it is generally / tmp OR / var / tmp OR / usr / tmp ). The directory can be set at MySQL startup.

HEAP table type is basically a storage type engine (MyISAM, InnoDB, HEAP etc).

ENUM is an enumeration type. An ENUM column definition includes a list of allowable values; each value in the list is called a "member" of the list. Every value stored in the column must equal one of the values in the list.

SET data type, like ENUM, is declared using a comma separated list of quoted strings that defines its valid members. But unlike ENUM, a given SET column may be assigned a value consisting of any combination of those members.

In other words, ENUM allows only one value out of list to store in field whereas SET allows one or more (or may be none) value out of list to store in field.

Clustered Index:

• There can be only one Clustered Index for a table.
• Usually made on Primary Key.
• The logical order of index matches the physical stored order of the rows on disk.
• If there is Primary Key, Clustered Index is by default created.

Non-Clustered Index:

• There can be only 249 Non-Clustered Index
• Usually made on any key.
• The logical order of index does not match the physical stored order of the rows on disk.
• Any key other than Primary Key if indexed, by default gets Non-Clustered Index.

Primary Key and Unique Key are Entity integrity constraints

Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.

Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values. (In oracle one null is not equal to another null).

PRIMARY KEY :

PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a way to uniquely identify any row in the table, so it shouldn't be used on any columns which allow NULL values. Your PRIMARY index should always be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead. There can only be one primary key in a table. Primary key can be created on multiple columns, e.g. Primary key (CustomerID, ProductID). This is called composite primary key. In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL.

UNIQUE KEY :

UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce structure on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data. Your database system may allow a UNIQUE index on columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (NULL is considered not equal to itself), though this is probably undesirable depending on your application. Can be more than one unique key in one table. Unique key can have NULL values.