Posts Tagged - sql

Don’t create indexes in every column. This slows things down on insert, delete or update operations.

You may create an index for columns that are common in WHERE, ORDER BY or GROUP BY clauses. You may also consider adding an index in columns that are used to relate other tables JOIN.

Index are best used for big domain fields such as ids, names, surnames. Don’t use them for male/female fields.

A good recommendation is to measure performance time before and after the creation of that index. If your index doesn’t improve performance remove it as it causes overhead.

Reference(s)

https://stackoverflow.com/questions/7744038/decision-when-to-create-index-on-table-column-in-database

Read More

UNION

The union sentence is used to accumulate results for two SELECT sentences.

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

We have the following tables

company1

company2

Read More

How to grant privileges for a database to a user, when both already exist.

In this case the database name will be project_node1, the user project_user and the password project_pass. All the following commands have to be executed as root or with a user with enough privileges.

-- list system users
SELECT user, host FROM mysql.user;

-- see current privileges
SHOW GRANTS FOR 'project_user'@'%';

-- delete all previous privileges (if needed)
-- REVOKE ALL PRIVILEGES ON `project_node1`.* FROM 'project_user'@'%';

-- grant new privileges and flush
GRANT ALL PRIVILEGES ON `project_node1`.* TO 'project_user'@'%';
FLUSH PRIVILEGES;

Reference(s)

https://serverfault.com/questions/115950/how-do-i-change-the-privileges-for-mysql-user-that-is-already-created

Read More

(All this commands are for MySQL)

Connection

Connect to DB from CLI

mysql -u {$user} -p    

User

• SELECT CURRENT_USER(); See current logged user

Change password when we know the old one

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('myNewPassword');

Read More

DDL Queries

• Show all dbs - SHOW DATABASES;
• Select one - USE {$db};
• Show tables in a db - SHOW TABLES;
• Show description of DB - DESCRIBE {$db}

Create new table

CREATE TABLE table_name (
	id numeric,
	description text);

Read More