MySQL

SQL tools

Performance tuning

MySQL Performance tuning script is here: https://raw.githubusercontent.com/BMDan/tuning-primer.sh/main/tuning-primer.sh File:Tuningscript.zip Download

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

INSERT INTO

Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Add / Drop indexes

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

MySQL CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:

CREATE INDEX idx_lastname
ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

ALTER TABLE table_name
DROP INDEX index_name;

Show indexes

To get the index of a table, you specify the table name after the FROM keyword. The statement will return the index information associated with the table in the current database.

You can specify the database name if you are not connected to any database or you want to get the index information of a table in a different database:

SHOW INDEXES FROM table_name;

SHOW INDEXES FROM table_name IN database_name;
or
SHOW INDEXES FROM database_name.table_name;

Note that INDEX and KEYS are the synonyms of the INDEXES, IN is the synonym of the FROM, therefore, you can use these synonyms in the SHOW INDEXES column instead. For example:

SHOW INDEX IN table_name FROM database_name;
or
SHOW KEYS FROM tablename IN databasename;

View mysql history

cat ~/.mysql_history

Select a database

USE <dbname>;

Search a table for a string by column

SELECT * FROM <tablename> WHERE <columname> LIKE '%somestring%';

Note that the % are essential and that the search string is case sensitive

Show a table schema

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='<tablename>';

Repair a database with .MYI/MYD suffix and mysqld offline

Steps: 1. Stop mysql service

systemctl stop mysql

2. CD to directory containing the database (table files)

cd /var/lib/mysql/drupal

3. Issue the repair command

nohup myisamchk -fpqqr --tmpdir=/mnt/piraid/mysqltmp searchindex &

4. Restart the mysql server

systemctl start mysql

This can take a long time (24 hours). The site will be offline while it happens. The cause of the table crash was dropping caches to free RAM. The DB server apparently ran out of free inodes while they were being cleared. The process can use 2x 3x the original table and index files - i.e. GB of space for a large table. The temp directory needs to have plenty of space an needs to be reliable if a network drive.

Repair a database table while mysqld is running

This method is now my preferred method. It leaves the database online and fixes the index without duplicating the entire table

Enter the mysql interpreter from root:

mysql

Issue SQL command:

USE <database name>;

Check a table for errors (optional - may take 2 hours):

CHECK TABLE 'tablename' FAST QUICK;

Repair the table (24 hours):

REPAIR TABLE 'tablename' QUICK;

Export parts of a database table to file

The syntax for exporting from joined tables (i.e. records from one table where other columns for those records are present in another table) is this:

SELECT column_name, another_column, etc_column FROM table1_name, joined_table2 WHERE table1_column =  table2_column INTO OUTFILE '/path/to/outfile.txt';

This is the command to export the current page revisions from mediawiki sites. The output directory seems to need to be owned my user mysql to be writable:

SELECT page_title, rev_timestamp, rev_sha1, page_id FROM page, revision WHERE page.page_id = revision.rev_page INTO OUTFILE '/var/lib/mysql/drupal/dump.txt';

Run mysql command from bash shell

Three methods:

mysql -u [username] -p somedb < somedb.sql

mysql -u [username] -p somedb -e [query]

mysql -u [username] -p -e "create database somedb"

mysql -u root -p somedb -e "select * from mytable"