Linux, PHP, LAMP, The Web in a blog

If you database has InnoDB tables, you will need to deactivate referential integrity while restoring data. Unfortunately, this can’t be done using the mysqldump utility. To do so, backup your database as you would normally. When done, open the SQL file and add the following statement at the very beginning:

SET FOREIGN_KEY_CHECKS=0;

…and add the following at the end of the file:

SET FOREIGN_KEY_CHECKS=1;

• use ENUM whenever possible
• do not be too generous with the varchar length
• NULL columns take more space and make computation harder - avoid using them, use empty strings or zeros.
• InnoDB-specific. Use a surrogate key which is a primary key whose value is not derived from application data; it is simply an AUTO-INCREMENT integer
• use indexes wisely (accordingly to the queries you are going to use). Benchmark your choice.
• chopping up query - if a query takes too long to execute (for example a query for purging) you should do it bit by bit in a loop. In this way you will avoid to lock tables for long time
• sometimes 3 one-table queries performs better that a query with 3 joins. This is called in-application join. In this way you have more change to use cache, locks will be shorter, you can store the tables on different servers (scalability) and you can reduce row access (it is not always easy for MySQL to optimize complex queries)

ls *.sql | awk ‘{printf(”mysql -u [username] -p[password] [databasename] < %s\n”, $1) | “/bin/sh” }’

Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:

SELECT something FROM tbl_name WHERE date_col >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) ;

//////////////////////////////////////////////////////////////////////////////////////////////////

–
– current week (starting with Sunday)
–
SELECT
COUNT(*) AS rows
FROM Orders
WHERE YEARWEEK(ODate) = YEARWEEK(CURRENT_DATE)
;
–
– last week (starting with Sunday)
–
SELECT
COUNT(*) AS rows
FROM Orders
WHERE YEARWEEK(ODate) = YEARWEEK(CURRENT_DATE - INTERVAL 7 DAY)
;
–
– last month
–
SELECT
COUNT(*) AS rows
FROM Orders
WHERE
SUBSTRING(ODate FROM 1 FOR 7) =
SUBSTRING(CURRENT_DATE - INTERVAL 1 MONTH FROM 1 FOR 7)
;

Learn more in the Date and Time Functions documentation section.

ALTER TABLE

ALTER TABLE table_name CHANGE column_name new_column_name int(11) NOT NULL DEFAUL 0;
ALTER TABLE table_name MODIFY column_name column_type;
ALTER TABLE table_name ADD column_name varchar(16) NOT NULL AFTER column_name2;
ALTER TABLE table_name ADD column_name varchar(32) AFTER column_name2
ALTER TABLE table_name ADD column_name varchar(32) FIRST
ALTER TABLE table_name DROP column_name;

Drop primary key:

ALTER TABLE table_name DROP PRIMARY KEY;

Create new primary key:

ALTER TABLE table_name ADD PRIMARY KEY(field_name);
if you want it to be auto_increment (you can’t make a non-primary-key field auto_increment):

ALTER TABLE table_name CHANGE field_name field_name mediumint unsigned not null auto_increment

Create new primary key made up by two fields:

ALTER TABLE table_name ADD PRIMARY KEY(field_name1, field_name2);

CREATE TABLE

CREATE TABLE student2 SELECT * FROM student WHERE class=’Four’
CREATE TABLE table_name1 LIKE database_name2.table_name2;

CREATE TABLE example_default_now (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, /* id related to the 'author' table */
data VARCHAR(100),
created TIMESTAMP DEFAULT NOW()
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT

INSERT INTO Table1 (column_1, column_2) SELECT column1, column2 FROM Table2

INSERT INTO table1 VALUES(1, “250,000″, “250000″);

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE

UPDATE table_name SET name=”ciao” WHERE id=1;

DELETE

DELETE table1.*

FROM table1

LEFT JOIN table2 ON table1.field1 = table2.field2

WHERE table2.field3 IS NULL

[like the corresponding SELECT query but you must specify the table from which you want to delete]

CREATING DUMP

mysqldump -uroot -p database_name > dump.sql
mysqldump -uroot -p database_name table_name > dump.sql
mysqldump -uroot -p –all-databases > dump.sql

mysqldump -d -uroot -p database_name > dump.sql oppure
mysqldump –no-data -uroot -p database_name > dump.sql
it outputs just the CREATE TABLE statements, very useful for diff

SELECT * INTO OUTFILE ‘file_name’ FROM table_name

DUPLICATE A ROW

INSERT INTO table1 (field1, field2, field3) SELECT field1, field2, field3 FROM table1 WHERE id=12;
INSERT A DUMP

use database_name [if the dump is about just a table]
source a.sql

Dropping (deleting) all the tables from a database

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

UTILITIES

DESC table_name;

show CREATE TABLE table_name; [more details than the previous one]
SHOW db_name [to check the tables]
select database() [to check the working database]

rehash [to let the consolle to autocomplete a brand new name of a table or a field]

SHOW processlist [show the list of all the processes running on the server]

GRANT

This way I create the new user and I give them the grant:

GRANT ALL ON database_name.* TO ‘db_user’@'db_host’ IDENTIFIED BY ‘user_password’; [mind the apostrophes]

CHEATS

ORDER BY rand() [random order]

INDEXES
CREATE INDEX index_name ON table_name(column_name)
DROP INDEX index_name ON table_name;

FULLTEXT INDEXES

ALTER TABLE table_name ADD FULLTEXT INDEX(column_name);
ALTER TABLE tableName DROP index `index_name_that_is_usually_the_column_name` ;

TROUBLESHOOTING / DEBUG
SHOW PROCESSLIST;
KILL

less “slow query log” (see my.cnf)

• Before enter a stored procedure you must change the delimiter for the instruction this way (for example):

Delimiter $

• SHOW PROCEDURE STATUS will show you all the existing stored procedures
• SHOW CREATE PROCEDURE procedure_name will show you the content of the procedure_name procedure
• CALL procedure_name to call the procedure called procedure_name
• In order to modify a stored procedure you before need to cancel and re-create it.