sql optimizations

B-tree is a self-balancing tree that maintains sorted data and allows operation in logarithmic time. B-trees can handle range queries on sorted data (<, ≤, >, ≥, between, in, is null, is not null)

B-tree index can also be used for queries that involve pattern matching operator LIKE or ~ if the pattern is a constant and the anchor is at the beginning of the pattern. For example, you can try matching queries column_name LIKE ‘Apple%’ or column_name ~ ‘^Apple’
But, querying ‘%macbook%’ or ‘%pro’ will not be efficient. For such queries, the query planner will resort to full-text sequential search which is not optimized.

Enter, GIN indexes.
GIN stands for Generalized Inverted Indexes.

We can create a GIN index to speed up text searches:

CREATE INDEX index_name ON table USING GIN (to_tsvector(‘english’, column_name));
The query above specifies that the English configuration can be used to parse and normalize the strings. And for the part of searching, a simple query to print the title of each row that contains the word friend in its body field is:

SELECT * FROM table WHERE to_tsvector(‘english’, column_name) @@ to_tsquery(‘english’, 'text_to_search);'
This will also find related words, for example, if you search friend, it will also search for words such as friends and friendly, since all these are reduced to the same normalized lexeme.

GIN index - better performance than b-tree for text search!
CREATE INDEX movies_name_idx_1 ON movies USING GIN (to_tsvector('english', title));
EXPLAIN ANALYZE SELECT * FROM movies WHERE to_tsvector('english', title) @@ to_tsquery('english', 'sea');
EXPLAIN ANALYZE SELECT * FROM movies WHERE to_tsvector('english', title) @@ to_tsquery('english', 'Pirate');


quide for best indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

self join:
http://www.programmerinterview.com/index.php/database-sql/what-is-a-self-join/

индексы:
где?

  • where
  • max min
  • order by group by
  • когда все извлекаемые поля проиндексированы, можно таблицу вообще не трогать
  • для выбора наименьшего количества совпадений. Если есть множественный индекс, то использоваться будет тот индекс, который находит самое маленькое число строк.
  • JOIN. Необходимо использовать одинаковые типы сравниваемых полей, иначе если необходимо преобразование типов, то без индекса.
красный зарос не использует индекс:

индексы не понимают вычисляемые выражения, при использовании не точных диапазонов( вычисляемые):
WHERE year(my_date) > 2010;
никаких LIKE;
никаких SELECT *
LIMIT 10000 это плохо.

insert into table values(`1`, `bla`); - плохо потому что если изменятся поля, то запрос не работает.


команда count считает не нулевые значения - т.к. id2 может быть NULL - будут непосчитаны NULL.

Storage engines:

SQL
http://www.sql-tutorial.ru/

WITH RECURSIVE
http://guilhembichot.blogspot.com/2013/11/with-recursive-and-mysql.html

индексы
http://ruhighload.com/post/%D0%A0%D0%B0%D0%B1%D0%BE%D1%82%D0%B0+%D1%81+%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81%D0%B0%D0%BC%D0%B8+%D0%B2+MySQL

http://www.mysql.ru/docs/man/MySQL_indexes.html
http://habrahabr.ru/post/70640/

storage engines:


Read about Storage Engines.
MyISAM:
The MyISAM storage engine in MySQL.
  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  • Full-text indexing.
  • Especially good for read-intensive (select) tables.
InnoDB:
The InnoDB storage engine in MySQL.
  • Support for transactions (giving you support for the ACID property).
  • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
  • Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
  • InnoDB is more resistant to table corruption than MyISAM.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future enhancements will be in InnoDB
MyISAM Limitations:
  • No foreign keys and cascading deletes/updates
  • No transactional integrity (ACID compliance)
  • No rollback abilities
  • Row limit of 4,284,867,296 rows (232)
  • Maximum of 64 indexes per row
InnoDB Limitations:
  • No full text indexing (Below-5.6 mysql version)
  • Cannot be compressed for fast, read-only
For brief understanding read below links:
  1. MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons
  2. MySQL Engines: MyISAM vs. InnoDB
  3. What are the main differences between InnoDB and MyISAM?
  4. MyISAM versus InnoDB
  5. What's the difference between MyISAM and InnoDB?
  6. MySql: MyISAM vs. Inno DB!
create database dump from remote host( http://dba.stackexchange.com/questions/9306/how-do-you-mysqldump-specific-tables):

params( http://stackoverflow.com/questions/5109993/mysqldump-data-only):
mysqldump --no-create-info ...
If you are using triggers you also need to include --skip-triggers
And if you are using the --databases ... option you also need to include --no-create-db

mysqldump --no-data -h server.com -u user -ppassword database t1 t2 > /home/file.sql
mysqldump --no-data -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz]


example:
mysqldump --no-data -u root -p123 phpmyadmin | gzip -9 - > /media/sf_deb_yandex/mysql/backup/daily/mysql_`date +%d%m%y`.tar.gz

mysqldump -u USERNAME -h HOST --no-create-info --skip-triggers YOURDATABASENAME YOURTABLENAME --where='id=12345678'
Access denied for user ... to database '...' when doing LOCK TABLES, use --single-transaction

import:
mysql -u user -ppassword dbname < dump.sql
gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename]

duplicate database( pipe): mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2
You can then move this file between servers with:

scp user@xxx.xxx.xxx.xxx:/path_to_your_dump/filename.sql.gz your_detination_path/
(where xxx.xxx.xxx.xxx is the server IP address)

And then import it with:

gunzip filename.sql.gz | mysql -u [user] -p [password] [database]

кодировки:
http://habrahabr.ru/post/10983/

truncate without checks for foreign key restrictions( src: http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table):
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table1; 
SET FOREIGN_KEY_CHECKS = 1;
***************************************************************
Есть таблица товаров.

CREATE TABLE `goods` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Она содержит следующие значения:
`id` `name`
1 Яблоки
2 Яблоки
3 Груши
4 Яблоки
5 Апельсины
6 Груши

Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:
(1,2), (4,1), (2,4), (6,3)

При решении задачи необходимо учесть, что пары (x,y) и (y,x) — одинаковы.

Приведите несколько вариантов решения задачи, какой вариант будет работать быстрее? Почему?
1.
CREATE TEMPORARY TABLE IF NOT EXISTS t1 ENGINE=MyISAM(SELECT  g1.id as id1, g2.id as id2 FROM `goods` as g1
join goods as g2 on g1.name = g2.name AND g1.id != g2.id);

select distinct least(t1.id1, t1.id2) as a, GREATEST(t1.id1, t1.id2) as b from t1

2.
CREATE TEMPORARY TABLE IF NOT EXISTS t1 ENGINE=MyISAM(SELECT  g1.id as id1, g2.id as id2 FROM `goods` as g1
join goods as g2 on g1.name = g2.name AND g1.id != g2.id);

SELECT DISTINCT t1.id1, t1.id2 FROM t1 LEFT JOIN (SELECT g1.id as id1, g2.id as id2 FROM `goods` as g1 join goods as g2 on g1.name = g2.name AND g1.id != g2.id) as t2 ON t1.id1=t2.id2 AND t1.id2=t2.id1 WHERE t2.id2 IS NULL OR t1.id1 <= t1.id2

тут если во временной таблице добавить индекс на полях id1, id2, то он не будет задействован из-за больше равно.
3.
CREATE TEMPORARY TABLE IF NOT EXISTS t1 ENGINE=MyISAM (SELECT  g1.id as id1, g2.id as id2 FROM `goods` as g1
join goods as g2 on g1.name = g2.name AND g1.id != g2.id);

SELECT id1, id2 FROM t1 WHERE id1 <= id2 UNION SELECT id1, id2 FROM (SELECT  g1.id as id1, g2.id as id2 FROM `goods` as g1
join goods as g2 on g1.name = g2.name AND g1.id != g2.id) as t2 WHERE id1 < id2

UNION будет работать медленнее всех.
**********************************************************************

src: https://www.netroby.com/view/3688
MySQL enable slow query log in milliseconds
MySQL enable slow query and general query log, just edit /etc/my.cnf
slow_query_log = 1
init-file=/etc/my-slow.sql
log-queries-not-using-indexes
slow_query_log-file = /opt/webserver/mysql/data/slow_query.log
general_log = 1
general_log_file = /opt/webserver/mysql/data/all_sql.log
the init-file argument specified boot strap sql 

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_init-file
set global long_query_time = 0.1;
long_query_time 0.1 means the sql execute time larger than 0.1 seconds will be logged

Комментарии

Популярные сообщения из этого блога

Дэвид Рок: Ваш мозг на работе - Разговоры в Гугле

kafka конспект однако

Отслеживание Процесса загрузки с PHP и JavaScript