In this tutorial, you have learned how to show all databases in the MySQL server using the SHOW DATABASES command or querying from the schemata table in the information_schema database. It returns the following result set: + -+ The following SELECT statement returns databases whose names end with 'schema' or 's'. If the condition in the LIKE clause is not sufficient, you can query the database information directly from the schemata table in the information_schema database.įor example, the following query returns the same result as the SHOW DATABASES command. Querying database data from information_schema Avoid joins to large tables Joining of large data sets using nested loops is very expensive. Even if you look at 1 fr rows or less, a full table scan may be faster. It is important to note that if the MySQL database server started with -skip-show-database, you cannot use the SHOW DATABASES statement unless you have the SHOW DATABASES privilege. Prefer full table scans to index accesses For large data sets, full table scans are often faster than range scans and other types of index lookups. If you want to query the database that matches a specific pattern, you use the LIKEclause as follows: SHOW DATABASES LIKE pattern įor example, the following statement returns the databases that ends with the string 'schema' SHOW DATABASES LIKE '%schema' I am running the following mysql command for the commits table and it didn't finish in more than 12 hours. After finishing importing records into the tables, I am trying to create indexes for these tables. The SHOW SCHEMAS command is a synonym for SHOW DATABASES, therefore the following command returns the same result as the one above: SHOW SCHEMAS Since these tables are quite big I had to load them using LOAD DATA INFILE with foreign key check off. To list all databases on a MySQL server host, you use the SHOW DATABASES command as follows: SHOW DATABASES Code language: SQL (Structured Query Language) ( sql )įor example, to list all databases in the local MySQL database server, first log in to the database server as follows: >mysql -u root -pĬode language: SQL (Structured Query Language) ( sql )Īnd then use the SHOW DATABASES command: mysql> SHOW DATABASES Summary: in this tutorial, you will learn how to use the MySQL SHOW DATABASES command to list all databases in the current MySQL database server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |