Comparison of status and variables in mysql optimization

Differences between mysql status and variables
 
First, you can use the following two commands to view the corresponding mysql system parameters.

Show status like '% abc % ';
Show variables like '% abc % ';
 
However, many people do not understand the differences between the two.

Status indicates that the status of the system cannot be changed. It is a parameter of the current running status of the system, which is described as follows:

Java code
Mysql> show status like 'innodb _ rows _ % ';
+ ---------------------- + --------- +
| Variable_name | Value |
+ ---------------------- + --------- +
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 1169098 |
| Innodb_rows_read | 7955216 |
| Innodb_rows_updated | 0 |
+ ---------------------- + --------- +
4 rows in set (0.00 sec)

The following sections describe:

| Innodb_rows_deleted | 0 | number of rows deleted from the innodb table. The value 0 indicates that no rows have been deleted.
| Innodb_rows_inserted | 1169098 | the number of insert rows in the innodb table, which indicates that 1169098 rows are inserted.
| Innodb_rows_read | 7955216 | number of rows obtained by executing select for the innodb table
| Innodb_rows_updated | 0 | number of rows involved in performing update for the innodb table
The preceding four parameters are the running status parameters of the innodb table. They cannot be manually modified, but can only be updated by the system. The purpose is obviously to tell the dba the current system status, so that the dba can perform optimization, the above four records tell the dba to read more than write at this time (I am executing insert into a select * from a, so the above data is generated), you can consider creating an appropriate index, if the read is 0, if the write size is large, you can consider deleting indexes and so on.

Java code

Mysql> show variables like 'query % ';
+ ------------------------------ + ---------- +
| Variable_name | Value |
+ ------------------------------ + ---------- +
| Query_alloc_block_size | 8192 |
| Query_cache_limit | 1048576 |
| Query_cache_min_res_unit | 4096 |
| Query_cache_size | 16777216 |
| Query_cache_type | ON |
| Query_cache_wlock_invalidate | OFF |
| Query_prealloc_size | 8192 |
+ ------------------------------ + ---------- +
7 rows in set (0.00 sec)

View the information about the query cache according to the above Mark. In this case, you can optimize the cache configuration according to the status. Note that the system uses the cache configuration information, which can be modified by setting or modifying the configuration file.

Mysql optimization keyword variables view various system configuration items

The mysqld server maintains two variables. Global variables affect global operations on the server. Session variables affect operations related to client connection.

When the server is started, all global variables are initialized to the default value. You can change these default values in the options file or the options specified in the command line. After the server is started, you can change the dynamic GLOBAL variables by connecting to the server and executing the set global var_name statement. To change global variables, you must have the SUPER permission.

The server also maintains session variables for each client connection. The client session variable is initialized using the current value of the corresponding global variable during connection. You can use the set session var_name statement to change the dynamic SESSION variable. You do not need special permissions to set session variables, but you can only change your session variables without changing the session variables of other customers.

Any client that accesses global variables can see changes to global variables. However, it only affects the client connected to which the corresponding session variable is initialized from the global variable after the change. It does not affect the session variables of connected clients (or even the clients that execute the set global statement ).

When you use the start option to set variables, you can use the suffix K, M, or G to indicate kilobytes, megabytes, or gigabytes. For example, the following command sets the buffer size of the key value to 16 megabytes when starting the server:

Mysqld -- key_buffer_size = 16 M

The suffix is case-sensitive. 16 m and 16 m are the same.

When running, use the SET statement to SET system variables. In this case, you cannot use a suffix, but you can use the following expressions for the value:

Mysql> SET sort_buffer_size = 10*1024*1024;

To explicitly specify whether to set GLOBAL or SESSION variables, use GLOBAL or SESSION options:

Mysql> set global sort_buffer_size = 10*1024*1024;

Mysql> set session sort_buffer_size = 10*1024*1024;

If none of the two options exist, the statement sets the session variable.


You can use the show variables statement to view system VARIABLES and their values.

Mysql> show variables;

1. back_log

Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests in a short time, this parameter takes effect. Then the main thread takes some time (although very short) to check the connection and start a new thread.

The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.

When you observe the MySQL process list, you will find a large number of 264084 | unauthenticated users | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value of back_log is 50.

2. basedir

The path of the MySQL main program, that is, the value of the -- basedir parameter.

3. bdb_cache_size

The size of the cache index and row arrangement buffer allocated to the BDB type data table. If the DBD type data table is not used, the -- skip-bdb parameter should be loaded when MySQL is started to avoid memory waste.

4. bdb_log_buffer_size

The cache index and row arrangement buffer size allocated to the BDB type data table. If the DBD type data table is not used, set this parameter to 0, or load the -- skip-bdb parameter when starting MySQL to avoid memory waste.

5. bdb_home

See the -- bdb-home option.

6. bdb_max_lock

Specifies the maximum number of lock table processes (10000 by default). This parameter can be used if a data table of the BDB type is used. If the bdb: Lock table is out of available locks or Got error 12 from... error is found during execution of large transaction processing or query, the value of this parameter should be increased.

7. bdb_logdir

Specifies the location where logs are stored when a BDB data table is used to provide services. That is, the value of -- bdb-logdir.

8. bdb_shared_data

If the -- bdb-shared-data option is used, the value of this parameter is On.

9. bdb_tmpdir

Temporary file directory of the BDB type data table. That is, the value of -- bdb-tmpdir.

10. binlog_cache_size

Specify the cache size used by SQL query statements during query request processing for binary logs. If it is frequently used for processing a large number of complex SQL expressions, you should increase the value of this parameter to improve performance.

11. bulk_insert_buffer_size

Specify that the MyISAM data table uses a special tree structure for caching. The whole bulk can accelerate the INSERT operation (INSERT... SELECT, INSERT... VALUES (...), (...),..., and load data infile. This parameter limits the cache size of the tree structure used by each thread. If it is set to 0, the cache acceleration function is disabled. Note: the cache operation corresponding to this parameter can only be performed by users into non-empty data tables! The default value is 8 MB.

12. character_set

The default character set of MySQL.

13. character_sets

The character set that MySQL can provide.

14. concurrent_inserts

If this parameter is enabled, MySQL allows the INSERT operation while executing the SELECT operation. If you want to disable this parameter, you can load the -- safe option when starting mysqld, or use the -- skip-new option. The default value is On.

15. connect_timeout

Specify the maximum number of seconds for the MySQL service to wait for a response to a connection message. After this time, MySQL returns bad handshake to the client.

16. datadir

Specifies the database path. That is, the value of the -- datadir option.

17. delay_key_write

This parameter is only valid for MyISAM data tables. There are the following types of values:

Off: if the create table... DELAYED_KEY_WRITES statement is used in the TABLE creation statement, all

DELAYED_KEY_WRITES;

On: If you use create table... DELAYED_KEY_WRITES in the TABLE creation statement, use this option (default );

All: all open data tables are processed according to DELAYED_KEY_WRITES.

If DELAYED_KEY_WRITES is enabled

The key buffer of the data table of the DELAYED_KEY_WRITES option, unless the data table is closed. This parameter greatly increases the speed of writing key values.

Degree. If this parameter is used, check all data tables: myisamchk -- fast -- force.

18. delayed_insert_limit

After the delayed_insert_limit row is inserted, the insert delayed processing module checks whether there are unexecuted SELECT statements. If yes, execute these statements before proceeding.

19. delayed_insert_timeout

The time for an insert delayed thread to wait for the INSERT statement before termination.

20. delayed_queue_size

The size of the queue allocated for processing insert delayed (in the unit of action ). If the queue is full, any insert delayed user must wait for the queue space to be released before continuing.

21. flush

Load the -- flush parameter when MySQL is started to enable this function.

22. flush_time

If this parameter is set to a non-zero value, all opened tables are shut down every flush_time second to release resources and sync to the disk. Note: this parameter is recommended only when Windows9x/Me is used or the current operating system resources are seriously insufficient!

23. ft_boolean_syntax

The search engine maintainer wants to change the operators allowed for logical full-text search. These are controlled by the ft_boolean_syntax variable.

24. ft_min_word_len

Specify the minimum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!

25. ft_max_word_len

Specifies the maximum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!

26. ft_max_word_len_for_sort

Specify the maximum length of keywords that can be used in the process of rapid full-text INDEX reconstruction using REPAIR, create index, or alter table. Keywords that exceed the length limit will be inserted at low speed. Increase the value of this parameter, MySQL will create a larger temporary file (this will reduce the CPU load, but the efficiency will depend on the disk I/O efficiency ), and put less key values in the memory in a sort.

27. ft_stopword_file

Read the list from the file specified by the ft_stopword_file variable. After the stopword list is modified, the FULLTEXT index must be rebuilt.

28. have_innodb

YES: MySQL supports InnoDB data tables; DISABLE: use -- skip-innodb to DISABLE InnoDB data tables.

32. init_file

Specify a file containing an SQL query statement. The file will be loaded when MySQL is started, and the SQL statement in the file will also be executed.

33. interactive_timeout

The number of seconds that the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect. Wait_timeout is also visible.

34. join_buffer_size

The buffer size used for all join operations (not the join operation using indexes ). The buffer zone allocates a buffer for each join between two tables. When an index is increased, increasing this value will allow you to get a faster join. (The best way to get a quick join is to increase the index .)

35. key_buffer_size

The buffer size used for index blocks, increasing the size of indexes (for all reads and writes) that can be better processed, so that you can afford that much. If you make it too large, the system will start to grow slowly. You must leave some space for the OS file system cache. To get more speed when writing multiple rows.

36. language

The language in which the user outputs the error message.

37. large_file_support

Enable support for large files.

29. have_bdb

YES: MySQL supports Berkeley data tables; DISABLE: use -- skip-bdb to DISABLE support for Berkeley data tables.

30. have_raid

YES: enable MySQL to support RAID.

31. have_openssl

YES: enables MySQL to support the SSL encryption protocol.

38. locked_in_memory

Use -- memlock to lock mysqld in the memory.

39. log

Record all query operations.

40. log_update

Enable update log.

41. log_bin

Enable binary log.

42. log_slave_updates

This parameter must be enabled if chainchain synchronization or synchronization between multiple Slave instances is used.

43. long_query_time

If the time used by a query exceeds the value of this parameter, the query operation is recorded in Slow_queries.

44. lower_case_table_names

1: MySQL uses lowercase letters for SQL operations;

0: disable this function.

Note: If this parameter is used, all data tables should be converted to lowercase letters before enabling.

45. max_allowed_packet

The maximum size of a query statement package. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. If the value is too small, an error will occur when processing the large package. This value must be added if a large BLOB column is used.

46. net_buffer_length

The communication buffer is reset to this size during query. Do not change the value of this parameter, but if the memory is insufficient, you can set it to the expected size of the query. (That is, the expected length of the SQL statement sent by the customer. If the statement exceeds this length, the buffer is automatically expanded until max_allowed_packet bytes .)

47. max_binlog_cache_size

Specify the maximum size of binary log cache. If the value is too small, MySQL will encounter an error when executing a complex query statement.

48. max_binlog_size

Specifies the maximum size of binary log files. The default value is 1 GB.

49. max_connections

Number of Customers allowed to connect to the MySQL server at the same time. If the value is exceeded, MySQL returns the Too connector connections error. However, MySQL can solve the problem by itself.

50. max_connect_errors

If an error connection is interrupted for the same host that exceeds the value of this parameter, the connection to the host is disabled. To unban the HOST, run: flush host ;.

51. max_delayed_threads

Do not start threads with more than this number to process the insert delayed statement. If you try to INSERT data to a new table after all the insert delayed threads are used, the row will be inserted, as if the DELAYED attribute was not specified.

52. max_heap_table_size

The maximum capacity that can be used by the memory table.

53. max_join_size

If you want to query the union of more than max_join_size records, an error is returned. If you want to execute a statement without a WHERE statement, which takes a lot of time and returns the join of millions of rows, you need to increase the value of this parameter.

54. max_sort_length

The number of bytes used for sorting BLOB or TEXT values (each value is used only in the header of max_sort_length; others are ignored ).

55. max_user_connections

Specifies the maximum number of connections from the same user. If it is set to 0, no restriction is imposed.

56. max_tmp_tables

(This parameter does not work yet ). The maximum number of temporary tables that a customer can open at the same time.

57. max_write_lock_count

When max_write_lock_count write locks occur, some locked read operations are allowed to begin. Avoid too many write locks and read operations in a long wait state.

58. myisam_recover_options


Mysql optimization keyword Status viewing system Status


1. back_log

Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests in a short time, this parameter takes effect. Then the main thread takes some time (although very short) to check the connection and start a new thread.

The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.

When you observe the MySQL process list, you will find a large number of 264084 | unauthenticated users | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value of back_log is 50.

2. basedir

The path of the MySQL main program, that is, the value of the -- basedir parameter.

3. bdb_cache_size

The size of the cache index and row arrangement buffer allocated to the BDB type data table. If the DBD type data table is not used, the -- skip-bdb parameter should be loaded when MySQL is started to avoid memory waste.

4. bdb_log_buffer_size

The cache index and row arrangement buffer size allocated to the BDB type data table. If the DBD type data table is not used, set this parameter to 0, or load the -- skip-bdb parameter when starting MySQL to avoid memory waste.

5. bdb_home

See the -- bdb-home option.

6. bdb_max_lock

Specifies the maximum number of lock table processes (10000 by default). This parameter can be used if a data table of the BDB type is used. If the bdb: Lock table is out of available locks or Got error 12 from... error is found during execution of large transaction processing or query, the value of this parameter should be increased.

7. bdb_logdir

Specifies the location where logs are stored when a BDB data table is used to provide services. That is, the value of -- bdb-logdir.

8. bdb_shared_data

If the -- bdb-shared-data option is used, the value of this parameter is On.

9. bdb_tmpdir

Temporary file directory of the BDB type data table. That is, the value of -- bdb-tmpdir.

10. binlog_cache_size

Specify the cache size used by SQL query statements during query request processing for binary logs. If it is frequently used for processing a large number of complex SQL expressions, you should increase the value of this parameter to improve performance.

11. bulk_insert_buffer_size

Specify that the MyISAM data table uses a special tree structure for caching. The whole bulk can accelerate the INSERT operation (INSERT... SELECT, INSERT... VALUES (...), (...),..., and load data infile. This parameter limits the cache size of the tree structure used by each thread. If it is set to 0, the cache acceleration function is disabled. Note: the cache operation corresponding to this parameter can only be performed by users into non-empty data tables! The default value is 8 MB.

12. character_set

The default character set of MySQL.

13. character_sets

The character set that MySQL can provide.

14. concurrent_inserts

If this parameter is enabled, MySQL allows the INSERT operation while executing the SELECT operation. If you want to disable this parameter, you can load the -- safe option when starting mysqld, or use the -- skip-new option. The default value is On.

15. connect_timeout

Specify the maximum number of seconds for the MySQL service to wait for a response to a connection message. After this time, MySQL returns bad handshake to the client.

16. datadir

Specifies the database path. That is, the value of the -- datadir option.

17. delay_key_write

This parameter is only valid for MyISAM data tables. There are the following types of values:

Off: if the create table... DELAYED_KEY_WRITES statement is used in the TABLE creation statement, all

DELAYED_KEY_WRITES;

On: If you use create table... DELAYED_KEY_WRITES in the TABLE creation statement, use this option (default );

All: all open data tables are processed according to DELAYED_KEY_WRITES.

If DELAYED_KEY_WRITES is enabled

The key buffer of the data table of the DELAYED_KEY_WRITES option, unless the data table is closed. This parameter greatly increases the speed of writing key values.

Degree. If this parameter is used, check all data tables: myisamchk -- fast -- force.

18. delayed_insert_limit

After the delayed_insert_limit row is inserted, the insert delayed processing module checks whether there are unexecuted SELECT statements. If yes, execute these statements before proceeding.

19. delayed_insert_timeout

The time for an insert delayed thread to wait for the INSERT statement before termination.

20. delayed_queue_size

The size of the queue allocated for processing insert delayed (in the unit of action ). If the queue is full, any insert delayed user must wait for the queue space to be released before continuing.

21. flush

Load the -- flush parameter when MySQL is started to enable this function.

22. flush_time

If this parameter is set to a non-zero value, all opened tables are shut down every flush_time second to release resources and sync to the disk. Note: this parameter is recommended only when Windows9x/Me is used or the current operating system resources are seriously insufficient!

23. ft_boolean_syntax

The search engine maintainer wants to change the operators allowed for logical full-text search. These are controlled by the ft_boolean_syntax variable.

24. ft_min_word_len

Specify the minimum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!

25. ft_max_word_len

Specifies the maximum length of the indexed keyword. Note: After this parameter value is changed, the index must be rebuilt!

26. ft_max_word_len_for_sort

Specify the maximum length of keywords that can be used in the process of rapid full-text INDEX reconstruction using REPAIR, create index, or alter table. Keywords that exceed the length limit will be inserted at low speed. Increase the value of this parameter, MySQL will create a larger temporary file (this will reduce the CPU load, but the efficiency will depend on the disk I/O efficiency ), and put less key values in the memory in a sort.

27. ft_stopword_file

Read the list from the file specified by the ft_stopword_file variable. After the stopword list is modified, the FULLTEXT index must be rebuilt.

28. have_innodb

YES: MySQL supports InnoDB data tables; DISABLE: use -- skip-innodb to DISABLE InnoDB data tables.

29. have_bdb

YES: MySQL supports Berkeley data tables; DISABLE: use -- skip-bdb to DISABLE support for Berkeley data tables.

30. have_raid

YES: enable MySQL to support RAID.

31. have_openssl

YES: enables MySQL to support the SSL encryption protocol.

32. init_file

Specify a file containing an SQL query statement. The file will be loaded when MySQL is started, and the SQL statement in the file will also be executed.

33. interactive_timeout

The number of seconds that the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect. Wait_timeout is also visible.

34. join_buffer_size

The buffer size used for all join operations (not the join operation using indexes ). The buffer zone allocates a buffer for each join between two tables. When an index is increased, increasing this value will allow you to get a faster join. (The best way to get a quick join is to increase the index .)

35. key_buffer_size

The buffer size used for index blocks, increasing the size of indexes (for all reads and writes) that can be better processed, so that you can afford that much. If you make it too large, the system will start to grow slowly. You must leave some space for the OS file system cache. To get more speed when writing multiple rows.

36. language

The language in which the user outputs the error message.

37. large_file_support

Enable support for large files.

38. locked_in_memory

Use -- memlock to lock mysqld in the memory.

39. log

Record all query operations.

40. log_update

Enable update log.

41. log_bin

Enable binary log.

42. log_slave_updates

This parameter must be enabled if chainchain synchronization or synchronization between multiple Slave instances is used.

43. long_query_time

If the time used by a query exceeds the value of this parameter, the query operation is recorded in Slow_queries.

44. lower_case_table_names

1: MySQL uses lowercase letters for SQL operations;

0: disable this function.

Note: If this parameter is used, all data tables should be converted to lowercase letters before enabling.

45. max_allowed_packet

The maximum size of a query statement package. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. If the value is too small, an error will occur when processing the large package. This value must be added if a large BLOB column is used.

46. net_buffer_length

The communication buffer is reset to this size during query. Do not change the value of this parameter, but if the memory is insufficient, you can set it to the expected size of the query. (That is, the expected length of the SQL statement sent by the customer. If the statement exceeds this length, the buffer is automatically expanded until max_allowed_packet bytes .)

47. max_binlog_cache_size

Specify the maximum size of binary log cache. If the value is too small, MySQL will encounter an error when executing a complex query statement.

48. max_binlog_size

Specifies the maximum size of binary log files. The default value is 1 GB.

49. max_connections

Number of Customers allowed to connect to the MySQL server at the same time. If the value is exceeded, MySQL returns the Too connector connections error. However, MySQL can solve the problem by itself.

50. max_connect_errors

If an error connection is interrupted for the same host that exceeds the value of this parameter, the connection to the host is disabled. To unban the HOST, run: flush host ;.

51. max_delayed_threads

Do not start threads with more than this number to process the insert delayed statement. If you try to INSERT data to a new table after all the insert delayed threads are used, the row will be inserted, as if the DELAYED attribute was not specified.

52. max_heap_table_size

The maximum capacity that can be used by the memory table.

53. max_join_size

If you want to query the union of more than max_join_size records, an error is returned. If you want to execute a statement without a WHERE statement, which takes a lot of time and returns the join of millions of rows, you need to increase the value of this parameter.

54. max_sort_length

The number of bytes used for sorting BLOB or TEXT values (each value is used only in the header of max_sort_length; others are ignored ).

55. max_user_connections

Specifies the maximum number of connections from the same user. If it is set to 0, no restriction is imposed.

56. max_tmp_tables

(This parameter does not work yet ). The maximum number of temporary tables that a customer can open at the same time.

57. max_write_lock_count

When max_write_lock_count write locks occur, some locked read operations are allowed to begin. Avoid too many write locks and read operations in a long wait state.

58. myisam_recover_options

Mysql show status details

Show status provides the server status information (like mysqladmin extended-STATUS ). The output is similar to the following display, although the format and number can be a bit different:
Meaning:

Aborted_clients indicates the number of dropped connections because the client fails to properly close the connection.
The number of connection times of the MySQL server that failed the Aborted_connects attempt.
The number of times that Connections attempted to connect to the MySQL server.
Created_tmp_tables: Number of implicit temporary tables created when the statement is executed.
The number of threads that Delayed_insert_threads is using to insert a delayed processor.
The number of rows written by Delayed_writes using insert delayed.
The number of rows in which Delayed_errors writes data using insert delayed (which may duplicate key values.
The number of times Flush_commands executes the FLUSH command.
The number of rows that Handler_delete requests to delete from a table.
The number of times the Handler_read_first request reads the first row of the table.
The Handler_read_key request number is based on the key-read row.
The number of times the Handler_read_next request reads a row based on a key.
The number of times the Handler_read_rnd request reads a row based on a fixed position.
The number of times Handler_update requests to update a row in the table.
The number of times a Handler_write request inserts a row into a table.
The number of Key_blocks_used blocks used for keyword cache.
The number of times Key_read_requests requests read a key value from the cache.
The number of times that Key_reads reads a key value from the disk physically.
Number of times that Key_write_requests requests write a key block to the cache.
The number of times that Key_writes physically writes a key-value block to a disk.
The maximum number of connections simultaneously used by Max_used_connections.
Not_flushed_key_blocks has been changed in the key cache but has not been cleared to the disk.
The number of rows that Not_flushed_delayed_rows is waiting to write in the insert delay queue.
The number of open tables in Open_tables.
The number of open files in Open_files.
Number of open streams in Open_streams (mainly used for log recording)
The number of open tables in Opened_tables.
The number of queries sent by Questions to the server.
Slow_queries takes more than long_query_time.
The number of connections currently opened by Threads_connected.
The number of threads whose Threads_running is not sleeping.
How many seconds does the Uptime server work.

Notes:

If Opened_tables is too large, your table_cache variable may be too small.
If key_reads is too large, your key_cache may be too small. The cache hit rate can be calculated using key_reads/key_read_requests.
If Handler_read_rnd is too large, you may have a large number of queries that require MySQL to scan the entire table or that you have not correctly used the join operation ).
Show variables displays the values of some MySQL system variables. You can also use the mysqladmin VARIABLES command to obtain this information. If the default value is not suitable, you can use the command line option to set the majority of these variables at mysqld startup.

Show processlist indicates which thread is running. You can also use the mysqladmin processlist command to obtain this information. If you have the process permission, you can see all the threads. Otherwise, you can only see your own threads. See the 7.20 KILL syntax. If you do not use the FULL option, only the first 100 characters of each query are displayed.