SQL Info

This post is to provide an easy to understand idea about the implementation of one of the most “talked about” principles of Object Oriented Programming - SOLID principles. As a programmer, there might come a time when we are so consumed in finishing the task at hand that we forget the world around us. And in the very same world, somewhere lost are the programming principles that we read about and planned to use next time. So let’s try to look at the SOLID principles in one of the simplest way possible so that we can practice them while coding. So let’s dig in to the SOLID principles at a glance. Single Responsibility:  A Class should be responsible for a single task. Open-Close Principle:  A Class should be open to extension and close to modification. Liskov Substitution:  A derived Class can be substituted at places where base Class is used. Interface Segregation:  Don’t make FAT Interfaces. i.e. Classes don’t have to override extra agreements that are not needed fo...

  ENUM   and   SET   columns provide an efficient way to define columns that can contain only a given set of values. See   Section 11.3.5, “The ENUM Type” , and   Section 11.3.6, “The SET Type” . Unless strict mode is disabled (not recommended, but see  Section 5.1.11, “Server SQL Modes” ), the definition of a  ENUM  or  SET  column acts as a constraint on values entered into the column. An error occurs for values that do not satisfy these conditions: An  ENUM  value must be one of those listed in the column definition, or the internal numeric equivalent thereof. The value cannot be the error value (that is, 0 or the empty string). For a column defined as  ENUM('a','b','c') , values such as  '' ,  'd' , or  'ax'  are invalid and are rejected. A  SET  value must be the empty string or a value consisting only of the values listed in the column definition separated by commas. ...

  An   ENUM   is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time. See  Section 11.3.1, “String Data Type Syntax”  for  ENUM  type syntax and length limits. The  ENUM  type has these advantages: Compact data storage in situations where a column has a limited set of possible values. The strings you specify as input values are automatically encoded as numbers. See  Section 11.7, “Data Type Storage Requirements”  for storage requirements for the  ENUM  type. Readable queries and output. The numbers are translated back to the corresponding strings in query results. and these potential issues to consider: If you make enumeration values that look like numbers, it is easy to mix up the literal values with their internal index numbers, as explained in  Enumeration Limitations . Using  ENUM  columns in ...

  Understanding the B-tree and hash data structures can help predict how different queries perform on different storage engines that use these data structures in their indexes, particularly for the   MEMORY   storage engine that lets you choose B-tree or hash indexes. B-Tree Index Characteristics Hash Index Characteristics B-Tree Index Characteristics A B-tree index can be used for column comparisons in expressions that use the  = ,  > ,  >= ,  < ,  <= , or  BETWEEN  operators. The index also can be used for  LIKE  comparisons if the argument to  LIKE  is a constant string that does not start with a wildcard character. For example, the following  SELECT  statements use indexes: SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%' ; SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%' ; In the first statement, only rows with  'Patrick' <=  key_col  < 'Patricl' ...

  Storage engines collect statistics about tables for use by the optimizer. Table statistics are based on value groups, where a value group is a set of rows with the same key prefix value. For optimizer purposes, an important statistic is the average value group size. MySQL uses the average value group size in the following ways: To estimate how many rows must be read for each  ref  access To estimate how many rows a partial join produces, that is, the number of rows produced by an operation of the form ( . . . ) JOIN tbl_name ON tbl_name . key = expr As the average value group size for an index increases, the index is less useful for those two purposes because the average number of rows per lookup increases: For the index to be good for optimization purposes, it is best that each index value target a small number of rows in the table. When a given index value yields a large number of rows, the index is less useful and MySQL is less likely to use it. The average val...

  Always check whether all your queries really use the indexes that you have created in the tables. Use the   EXPLAIN   statement, as described in   Section 8.8.1, “Optimizing Queries with EXPLAIN” .