MyPHP - All about PHP.

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 relevant rows.  Five Types of Indexes When you create an index or add one to an existing table, you’ll create it as one of several types of indexes. A unique index is one in which all column values must be unique. In a single column unique index there can be no duplication of values in the column being indexed. In a multi-column unique index the values can be duplicated in a single column, but the combination of column values in each row must be unique. You use a unique index to prevent duplicate values and you often define the index after a table has been created. A primary key is a unique index in which no value can be NULL. Every row must have a value for the column or combination of columns. You would usually define a primary key on the smallest number of columns possible because of this, and most of the time a

Concat two columns in SELECT query - SELECT employeeid, CONCAT(firstname, lastname) AS employee_name FROM employee; Concat by adding space -> CONCAT(firstname, ' ', lastname) AS employee_name Write a query to find even records in MYSQL.   SELECT * FROM EMPLOYEE WHERE id IN(SELECT id FROM EMPLOYEE WHERE id%2 = 0); Write a query to find odd records in MYSQL. SELECT * FROM EMPLOYEE WHERE id IN(SELECT id FROM EMPLOYEE WHERE id%2 <> 0); Get duplicate records from table SELECT name, count(name) FROM employee GROUP BY name HAVING(name) > 1 Get 4th Highest salary with limit function and without using limit functions SELECT name,email from users ORDER BY DESC LIMIT 4,1 Finding the highest salary select MAX(Salary) from Employee; Finding the 2nd highest salary Query-1 SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee); Query-2 select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee ) Finding the nth highest

Here are a few of the major differences between InnoDB and MyISAM: InnoDB has row-level locking. MyISAM only has full table-level locking. InnoDB has what is called referential integrity which involves supporting foreign keys (RDBMS) and relationship constraints, MyISAM does not (DMBS). InnoDB supports transactions, which means you can commit and roll back. MyISAM does not. InnoDB is more reliable as it uses transactional logs for auto recovery. MyISAM does not.

InnoDB is a storage engine in MySQL. InnoDB's greatest strengths are: Its design follows the ACID model, with transactions featuring commit, rollback, and crash recovery capabilities to protect user data. Row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent reads increase multi-user concurrency and performance. Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables. InnoDB tables arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups. You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query. InnoDB Limitations : No full-text indexing (Below-

 Normalization is the technique of organizing the data into multiple tables to minimize data redundancy. Data redundancy means the reputation of data at multiple places. Different issues can be observed while insertion, Updation, Deletion. Unnecessary data reputation will increase the size of the database and leads to more issues. Normalization will break the table into two different tables.

MySQL is one of the most popular open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). Useful tips to improve MySQL Query for speed and performance. 1. Optimize Your Database You need to know how to design schemas to support efficient queries. Well-designed queries and schema are crucial for your application to work properly. Optimizing your MySQL queries alone will not bring excellent database performance. A well-structured database is crucial along with an optimized query. The following steps will help you to optimize your database. a. Normalize Tables b. Use Optimal Data Types c. Avoid Null Values d. Avoid Too Many Columns 2. Optimize Joins 3. Index All Columns Used in ‘where’, ‘order by’, and ‘group by’ Clauses 4. Use Full-Text Searches - MySQL full-text search (FTS) is far faster than queries using wildcard characters. To add a full-text search index to the students’ sample table, we can use the below MySQL command: mysql>Alter tab