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.##-a system with little memory (<= 64M) where MySQL is only u  sed# 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 dire Ctory Is/usr/local/mysql/var) or# ~/.my.cnf to set user-specific options.## in this file, you can use the all long options th At 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_passwordport= 3306socket=/tmp/mysql.sock # here follows entries for some specific programs# the MySQL server[mysqld]port= 3306socket=/tmp/mysql.sockskip-lockingke Y_buffer = 16kmax_allowed_packet = 1mtable_cache = 4sort_buffer_size = 64kread_buffer_size = 256Kread_rnd_buffer_size = 25 6knet_buffer_length = 2KThread_stack = 64kdatadir=/www/mysql/datalog-slow-queries=/www/log/mysql/slowquery.loglong_query_time=2# Don ' t Listen on a TCP/IP port at all. This can is a security enhancement,# if all processes then need to connect to mysqld run in the same host.# all Interactio N with Mysqld must is made via Unix sockets or named pipes.# Note that using the this option without enabling named Pipes on W indows# (using the "enable-named-pipe" option) would render mysqld useless!# #skip-networkingserver-id= # uncomment the FO Llowing if you want to log updates#log-bin=mysql-bin# uncomment the following if you is not using BDB tables#skip-bdb# Un  Comment 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]quickmax_allowed_packet = 16m[mysql]no-auto-rehash# Remove the next Comment character if you is not familiar with sql#safe-updates[isamchk]key_buffer = 8msort_buffer_size = 8m[myisamchk]key _buffer = 8msort_buffer_size = 8m[mysqlhotcopy]interactive-timeout

Reply to discussion (solution)

Build Pid,id compound index, PID in front of compound index, ID at the end of compound index number

LS +
Look at the extra section, the Using Filesort is a big cause of poor performance.
The reason is that the search for PID and ID and the order by section are not indexed.
Plus (aggregated index of Pid,id)

Index the key field.

LS +
Look at the extra section, the Using Filesort is a big cause of poor performance.
The reason is that the search for PID and ID and the order by section are not indexed.
Plus (aggregated index of Pid,id)

What is the specific imperative, novice does not understand AH

Build Pid,id compound index, PID in front of compound index, ID at the end of compound index number
What is the specific imperative, novice does not understand AH

LS +
Look at the extra section, the Using Filesort is a big cause of poor performance.
The reason is that the search for PID and ID and the order by section are not indexed.
Plus (aggregated index of Pid,id)

Do you create a primary key without automatically creating an index?

LS +
Look at the extra section, the Using Filesort is a big cause of poor performance.
The reason is that the search for PID and ID and the order by section are not indexed.
Plus (aggregated index of Pid,id)

Do you create a primary key without automatically creating an index?

Baidu primary key index, federated Index

ALTER TABLE XXX Add index pid_id (PID, id);

You probably don't have a primary key index, otherwise explain won't show up like this.

Go to phpMyAdmin Home right click on Show runtime information, below you run the table, look at those value red lines, and then notice the description behind, it can help you adjust MySQL to optimize performance.

In addition, InnoDB or MyISAM?

MyISAM words, you can try to adjust.

Key_buffer = 16K = "Key_buffer = 16M
Table_cache = 4 = "Table_cache = 512
Sort_buffer_size = 64K = "Sort_buffer_size = 2M
Read_rnd_buffer_size = 256K = "Read_rnd_buffer_size = 2M

Add a myisam_sort_buffer_size = 16M

Then restart Mysqld and look at the situation. MySQL CPU consumption is too high to ignore.

The PID has been indexed and the ID is the primary key.
Why it's so slow, it makes no sense

Although the PID and ID are indexed, only one can be used at a time, so you must build a federated index

Make a partition ....

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
The red part of the statement caused by the disaster, the full table scan, plus index also wood use.
Just remove the red ones.

Test a bit seemingly not, landlord or sub-table it.

Remove the order by ID DESC in your statement, and if your ID is automatically edited, configure it in my.cnf to sort by desc, then check directly.

Try the joint index.

Otherwise, try it with a subquery.

Also, is the size of the table larger than the size of the memory?
Memory 512M, the system also need to use some, can leave MySQL not much.

