How can I optimize MySQL 500 million data queries in over seconds?

How to optimize MySQL 500 million data query in over 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_passwordport= 3306socket= /tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port= 3306socket= /tmp/mysql.sockskip-lockingkey_buffer = 16Kmax_allowed_packet = 1Mtable_cache = 4sort_buffer_size = 64Kread_buffer_size = 256Kread_rnd_buffer_size = 256Knet_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 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-networkingserver-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]quickmax_allowed_packet = 16M[mysql]no-auto-rehash# Remove the next comment character if you are 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)

Create a pid and id composite index. The PID is before the composite index, and the ID is at the end of the composite index.

Ls +
In the extra section, Using filesort is the next major cause of low performance.
The reason is that the search and order by parts of the pid and id are not indexed.
Add (pid, id aggregation index)

Add an index to the key fields.

Ls +
In the extra section, Using filesort is the next major cause of low performance.
The reason is that the search and order by parts of the pid and id are not indexed.
Add (pid, id aggregation index)

What is the specific imperative?

Create a pid and id composite index. The PID is before the composite index, and the ID is at the end of the composite index.
What is the specific imperative?

Ls +
In the extra section, Using filesort is the next major cause of low performance.
The reason is that the search and order by parts of the pid and id are not indexed.
Add (pid, id aggregation index)

Will indexes be automatically created when a primary key is created?

Ls +
In the extra section, Using filesort is the next major cause of low performance.
The reason is that the search and order by parts of the pid and id are not indexed.
Add (pid, id aggregation index)

Will indexes be automatically created when a primary key is created?

Baidu primary key index, joint index

Alter table xxx add index pid_id (pid, id );

You may not have created a primary key index. Otherwise, the explanation will not be displayed.

Go to the right side of the phpmyadmin homepage and click Show Runtime Information. the following shows the running status of your table. check the red lines of values and pay attention to the Description below. this helps you adjust mysql to the optimal performance.

In addition, does it mean innodb or myisam?

For myisam, try to adjust it.

Key_buffer = 16 K =, key_buffer = 16 M
Table_cache = 4 = "table_cache = 512
Sort_buffer_size = 64 K = "sort_buffer_size = 2 M
Read_rnd_buffer_size = 256 K = "read_rnd_buffer_size = 2 M

Add myisam_sort_buffer_size = 16 M

Restart mysqld to check the situation. Ignore the high cpu usage of mysql.

Pid has been indexed, and id is the primary key.
Why is it so slow? no reason

Although both pid and id have an index, only one of them can be used at a time. Therefore, you must create a joint index.

Make a partition ....

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 red part of the statement caused the trouble, the full table scan, index addition is also used.
Just remove the red part.

It seems that the test won't work. let's just split the table.

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

Try the combined index.

Or try a subquery.

Also, is the table larger than the memory size?
The memory is 512 MB, and the system needs to be used out, leaving little memory for MYSQL.