SQL Indexes Explained: MySQL Indexes & Storage Engines
In this sql tutorial video we’ll walk you through mysql indexes and mysql storage engines.
SQL indexes work differently in different MySQL server storage engines. Here are some facts.
1) Number of indexes: the maximum number of indexes per MyISAM and InnoDB tables are 64, the maximum number of columns per index in both MySQL storage engines is 16.
2) The maximum sql key (sql index) length: the maximum sql index length for InnoDB is 3500 bytes – the maximum mysql index length for MyISAM is 1000 bytes.
3) In the InnoDB storage engine in MySQL fulltext indexes have 36 stopwords, while the MyISAM stopword list is a little bit bigger with 143 stopwords.
4) A decent mysql tutorial will tell you that MyISAM was the only storage engine with the support for full-text search options until MySQL 5.6.4 came around.
5) In MySQL server, InnoDB locks rows when it accesses them, so a reduced number of rows InnoDB accesses can reduce locks.
6) The MySQL server allows you to use more than one SQL index on the same column.
7) In MySQL, different storage engines have different default types of SQL indexes: for example, for the MEMORY storage engine the default index type is hash, for other storage engines (InnoDB, MyISAM, etc.), the default index type is B-Tree.
Enjoy the video!
Some of the most popular SQL interview questions and answers can be found below:
Q: MySQL vs PostgreSQL. What are the main differences?
A: The main difference is that PostgreSQL is an object-relational DBMS while MySQL is a relational DBMS.
Q: MySQL password not working. What do I do?
A: Follow the SQL tutorial here: https://youtube.com/shorts/CBdyaqqEJqY
Q: MySQL MyISAM vs InnoDB. Which one to use?
A: Use InnoDB unless you intend to run COUNT(*) queries because MyISAM stores the row count inside of itself.
Q: What OS is the mysql my.ini file applicable to?
A: The file is applicable to a Windows OS.
Q: What’s the mysql my.cnf location?
A: The location of the file can vary, but some of the most common locations include:
* /etc/my.cnf (the most common location.)
* /etc/mysql/my.cnf
* /[datadirectory]/my.cnf
* ~/.my.cnf.
Q: What are some of the most popular SQL commands?
A: CRUD queries are the most popular SQL queries. They can be used to create (insert), read (select), update, or delete data.
Q: How dangerous is SQL injection? How to protect our web applications from this attack?
A: SQL injection is one of the most dangerous attacks targeting applications today. To protect our web applications from sql injection, we need to sanitize our inputs and never provide user input to a database.
Q: What file should be used for mysql server configuration?
A: my.cnf or my.ini.
Q: What are the most important settings we should optimize in MySQL?
A: Optimize these settings (these mysql server settings are specific to InnoDB or Percona XtraDB storage engines):
* innodb-data-file-path: this parameter defines the location of the InnoDB system tablespace (the file where all InnoDB data is stored.)
* innodb-buffer-pool-size: this parameter defines the size of the InnoDB buffer pool and it is the most important parameter in the entire MySQL server infrastructure related to its primary storage engine – InnoDB.
* innodb-log-file-size: this MySQL server InnoDB parameter defines the log file size. The larger it is, the better your mysql server performance will be, but the longer recovery time after a crash will be required too.
* innodb-log-buffer-size: this mysql server parameter is used to write to the log files on disk.
* innodb-flush-log-at-trx-commit: this MySQL install parameter controls the balance between strict database ACID compliance and high performance.
* innodb-flush-method: this MySQL install parameter defines the method that is used to flush data to InnoDB log files.
Q: What storage engine should we use in our MySQL install?
A: Use either InnoDB or Percona XtraDB. If there’s a necessity to run COUNT(*) queries, use MyISAM, because InnoDB and Percona XtraDB don’t store the row count internally while MyISAM does.
Q: What is the most frequently used type of SQL index?
A: A B-tree SQL index is used the most frequently in mysql server as well as other RDBMS systems.
#shorts #reels #database #mysql #web #webdevelopment