Mysql Nested Query Tweak

Do not use mysql nested queries for performance. Here I will share a little database table and two queries and their response times:
#Experiment is done by Mustafa TURAN(http://vpslife.blogspot.com/)#
#Experiment purpose to see how a nested query affects MYSQL Performance #
#Experiment Hardware:#
#--RAM: 1.5GB#
#--CPU: AMD Athlon(tm) 64 X2 Dual Core Processor 4000#
#Experiment Software:#
#--MYSQL 5(MYISAM)#
#--Centos5.2#
#Notes: 1k=1000#
Experiment tables
Table #1: links

database table : links(l_id, l_source, l_status, l_kb)

total rows: 220k

Table #2: filelinks

database table : filelinks(f_id, l_id)

total rows: 1500k

Nested query

Query: SELECT l_id, l_source, l_status, l_kb FROM links WHERE links.l_id IN (SELECT l_id FROM filelinks WHERE filelinks.f_id = 1);

Response: Showing rows 0 - 0 (1 total, Query took 2.9740 sec)

Inner Join(same query with inner join method)

Query: SELECT distinct(l_id), l_source, l_status, l_kb FROM links INNER JOIN (SELECT l_id as xL_id FROM filelinks WHERE filelinks.f_id = 1) xLinks ON links.l_id =
xLinks.xL_id;

Response: Showing rows 0 - 0 (1 total, Query took 0.0003 sec)

This example make you to see how dangerous using nested queries. I hope this experiment will save your CPU usage by MYSQL!

My detailed /etc/my.cnf file:

#Mustafa Turan (http://vpslife.blogspot.com/)#

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
port = 3306
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 2M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 128
query_cache_size= 64M
thread_concurrency = 4
thread_stack = 1M
max_connections = 250
skip-bdb
skip-innodb

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 16M
write_buffer = 16M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

How to learn linux system(hardware) properties?

To learn system(hardware,sofware) properties linux has such commands:
Operating System Version:

uname -r

Memory Information:

cat /proc/meminfo

CPU Information:

cat /proc/cpuinfo

Easy Way(smart info):

uname -rs;less /proc/meminfo grep MemTotal;less /proc/cpuinfo grep
"model name"

Aktive System Resource Usages:
(all users and all activity)

top -s

(specified user)

top -u username

Change Hostname on Centos 5x

On terminal screen apply the commands:

vi /etc/sysconfig/network

Then change HOSTNAME=xxxx.xxx line to HOSTNAME=your_desired_hostname
You have to save the data and quit(thats all!):

wq!

Copy Remote Database to Local Database

Remote mysqldump

Run this one line command as a user:

mysqldump --opt --compress --user=REMOTE_DB_USERNAME --password=REMOTE_DB_PASS --host=REMOTE_DB_HOSTNAME REMOTE_DB_NAME mysql --user=LOCAL_DB_USERNAME --password=LOCAL_DB_PASSWORD --host=LOCAL_DB_HOSTNAME -D LOCAL_DB_NAME -C LOCAL_DB_NAME

Notes: You have to enable remote mysql on remote computer! (To do this you have to add ip address of local computer to remote computer's db remote access ip list)