How to optimize MySQL 500 million data queries in over seconds

How to optimize mysql 500 million data query in more than 2013 seconds? This post was last edited by dz215136304 in 1511-08-: 33: 52 In linux & nbsp; mysql & nbsp; 3 million how to optimize data query for more than 500 seconds, where pid has been indexed, id is the primary key SELECT & nbsp; id, pid, keyWords, shortUrl & mysql 3 million data query how to optimize it in over 500 seconds

This post was last edited by dz215136304 at 11:33:52

In linux, how can I optimize mysql 3 million data query for more than 500 seconds? the pid has been indexed and the id is the primary key.


SELECT id, pid, keyWords, invalid url from keywords WHERE pid = 0 order by id desc limit 50






The description is 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)

What's wrong with the high cpu usage of mysql? Memory: 512 MB

The configuration file is as follows:

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

# The following options will 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 be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will 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 are NOT using BDB tables
#skip-bdb

# Uncomment the following if you are 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 25 % 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