MySQL optimization 2

Advantages and disadvantages of SQL statements

Select * From node_revisions where NID in (select max (NID) from node)
Better
Select @ maxid: = max (NID) from node;
Select * From node_revisions where nid = @ maxid

Select * from node where NID> 10 or created> 1209793459;
Better (if either of NID and created is not index)
Select * from node where NID> 10
Union
Select * from node where create> 1209793459

Select * from node where NID in (select NID from term_node where TID <10)
Better (using subquery is not a good choice in common)
Select N. * from node N left join term_node t on T. nid = n. NID where TID <10

Group by optimization

the results of group by will be sort as group by column (s) in default, so if you don't want to sort result, add order by NULL:
select count (NID) from node group by VID
explain:
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
| 1 | simple | node | all | null | null | 23 | using temporary; using filesort

Select count (NID) from node group by VID order by null
Explain:
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
| 1 | simple | node | all | null | 23 | using temporary

Sometimes group by SQL statement is too slow in large table, the problem maybe is the variable tmp_table_size too small, which is 32 m in default, so you need change it to the bigger value.

Optimization of create Index

When you create index on varchar or char column, partial index is better than all index, especially in large column:
Create index name_index on users (name (10 ));
It will create index only the first 10 characters.

MySQL Server Configuration

Add configuration for MySQL Section
[Mysqld]
Skip-name-resolve # don't resolve hostnames

Compile MySQL on Linux

Config. MySQL. Sh
#! /Bin/bash

EXE = configure

$ EXE -- prefix =/opt/MySQL -- localstatedir =/opt/MySQL/data -- With-mysqld-user = MySQL -- sysconfdir =/opt/MySQL/etc -- With-Unix- socket-Path =/opt/MySQL/temp/MySQL. sock -- With-TCP-Port = 3307 -- With-charset = utf8 -- With-extra-charsets = all -- With-mysqld-ldflags =-all-static -- With-client-ldflags =-all-static -- enable-validator -- enable-thread-safe-client -- With-pthread -- without-Debug -- without -- isam

# Configure parameters:
# -- Prefix =/home/MySQL/\ specify the installation directory
# -- Without-Debug \ remove the debug mode
# -- With-extra-charsets = GBK, gb2312, utf8 \ added support for GBK, gb2312, and utf8 Chinese Characters
# -- With-pthread \ force use of the pthread Library (POSIX thread Library)
# -- Enable-javaser \ use the assembly version of some character Functions
# -- Enable-thread-safe-client \ compile the client in thread mode
# -- With-client-ldflags =-all-static \ compile the client in pure static mode
# -- With-mysqld-ldflags =-all-static \ compile the server in pure static mode
# -- Without-isam \ remove the isam table type, which is rarely used now. The isam table is a platform-dependent table.
# -- Without-InnoDB \ remove InnoDB table support. InnoDB is a table that supports transaction processing and is suitable for enterprise-level applications.

Problems in MySQL

Remote Access failure

1. mysql> grant all privileges on *. * To 'user' @ '%' identified by "1234 ";

2. $ sudo gedit/etc/MySQL/My. CNF
Earlier versions
> Skip-networking => # Skip-networking
New Version
> Bind-address = 127.0.0.1 => bind-address = IP address of your machine
127.0.0.1 allow local access