MySQL 3 million data query more than 500 seconds how to optimize

MySQL 3 million data query more than 500 seconds how to optimize AH
This post was last edited by dz215136304 on 2013-08-15 11:33:52

Linux MySQL 3 million data query more than 500 seconds how to optimize Ah, where the PID has been indexed, ID is the primary key


SELECT Id,pid,keywords,shorturl from KeyWords WHERE pid=0 ORDER by ID DESC LIMIT 50






Explain as follows:

Mysql> explain SELECT Id,pid,keywords,shorturl from  keyWords  WHERE pid=0 ORDER by ID DESC LIMIT 50;
+----+-------------+----------+------+---------------+------+---------+-------+---------+---------------------- -------+
| ID | Select_type | Table    | type | possible_keys | key  | key_len | ref   | rows    | Extra                       |
+----+-------------+----------+------+---------------+------+---------+-------+---------+---------------------- -------+
|  1 | Simple      | keywords | ref  | pid |           PID |  4       | const | 2452523 | Using where; Using Filesort |
+----+-------------+----------+------+---------------+------+---------+-------+---------+---------------------- -------+
1 row in Set (8.18 sec)

In addition MySQL CPU occupies very high what is the matter? Memory: 512M

The configuration file is as follows:

# Example MySQL config file for small systems.
#
# This was for a system with little memory (<= 64M) where MySQL was only used
# from time to time and it's important that the mysqld daemon
# doesn ' t use much resources.
#
# can copy this file to
#/ETC/MY.CNF to set global options,
# MYSQL-DATA-DIR/MY.CNF to set server-specific options
# installation This directory is/usr/local/mysql/var) or
# ~/.MY.CNF to set user-specific options.
#
# in the This file, you can use the all long options, which a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options would be passed to all MySQL clients
[Client]
#password = Your_password
Port= 3306
socket=/tmp/mysql.sock

# here follows entries for some specific programs

# The MySQL server
[Mysqld]
Port= 3306
socket=/tmp/mysql.sock
Skip-locking
Key_buffer = 16K
Max_allowed_packet = 1M
Table_cache = 4
Sort_buffer_size = 64K
Read_buffer_size = 256K
Read_rnd_buffer_size = 256K
Net_buffer_length = 2K
Thread_stack = 64K
Datadir=/www/mysql/data
Log-slow-queries=/www/log/mysql/slowquery.log
long_query_time=2


# Don ' t listen on a TCP/IP port at all. This can is a security enhancement,
# If all processes this need to connect to mysqld run on the same host.
# All interaction with Mysqld must is made via Unix sockets or named pipes.
# Note that the using this option without enabling named Pipes on Windows
# (using the "enable-named-pipe" option) would render mysqld useless!
#
#skip-networking
Server-id= 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# Uncomment the following if you is not using BDB tables
#skip-BDB

# Uncomment the following if you is using InnoDB tables
#innodb_data_home_dir =/usr/local/mysql/var/
#innodb_data_file_path = Ibdata1:10m:autoextend
#innodb_log_group_home_dir =/usr/local/mysql/var/
#innodb_log_arch_dir =/usr/local/mysql/var/
# you can set: _buffer_pool_size up to 50-80%
# of RAM But beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set: _log_file_size to% of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash
