2

I have a very simple query

select count(*) from user_access where user_name='mike'

on a table

+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| user_name | varchar(32) | NO   | PRI | NULL              |                             |
| sample_id | int(11)     | NO   | PRI | 0                 |                             |
| updated   | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-------------+------+-----+-------------------+-----------------------------+

I have tried this over the network and on the host itself and either way it hangs

| 14051 |  | localhost                     | nir  | Query   |   677 | Sending data | select count(*) from user_access where user_name='mike'

Here is a list of the buffers & caches & such

+---------------------------------------------------+----------------------+
| Variable_name                                     | Value                |
+---------------------------------------------------+----------------------+
| binlog_cache_size                                 | 32768                |
| binlog_stmt_cache_size                            | 32768                |
| bulk_insert_buffer_size                           | 8388608              |
| delayed_queue_size                                | 1000                 |
| innodb_additional_mem_pool_size                   | 8388608              |
| innodb_buffer_pool_size                           | 134217728            |
| innodb_log_buffer_size                            | 8388608              |
| innodb_log_file_size                              | 5242880              |
| innodb_purge_batch_size                           | 20                   |
| join_buffer_size                                  | 131072               |
| key_buffer_size                                   | 16777216             |
| key_cache_block_size                              | 1024                 |
| large_page_size                                   | 0                    |
| max_binlog_cache_size                             | 18446744073709547520 |
| max_binlog_size                                   | 104857600            |
| max_binlog_stmt_cache_size                        | 18446744073709547520 |
| max_heap_table_size                               | 16777216             |
| max_join_size                                     | 18446744073709551615 |
| max_long_data_size                                | 67108864             |
| max_relay_log_size                                | 0                    |
| metadata_locks_cache_size                         | 1024                 |
| myisam_data_pointer_size                          | 6                    |
| myisam_max_sort_file_size                         | 9223372036853727232  |
| myisam_mmap_size                                  | 18446744073709551615 |
| myisam_sort_buffer_size                           | 8388608              |
| performance_schema_events_waits_history_long_size | 10000                |
| performance_schema_events_waits_history_size      | 10                   |
| preload_buffer_size                               | 32768                |
| profiling_history_size                            | 15                   |
| query_alloc_block_size                            | 8192                 |
| query_cache_size                                  | 16777216             |
| query_prealloc_size                               | 8192                 |
| range_alloc_block_size                            | 4096                 |
| read_buffer_size                                  | 131072               |
| read_rnd_buffer_size                              | 262144               |
| sort_buffer_size                                  | 2097152              |
| sql_max_join_size                                 | 18446744073709551615 |
| thread_cache_size                                 | 8                    |
| tmp_table_size                                    | 16777216             |
| transaction_alloc_block_size                      | 8192                 |
| transaction_prealloc_size                         | 4096                 |
+---------------------------------------------------+----------------------+

If there is other data that someone would like to see, I'll get it.

There is a pretty vanilla query. Can anyone suggest a place to look for the problem?

The output of EXPLAIN is

+----+-------------+-------------+-------+---------------+---------+---------+--‌​----+-------+--------------------------+ 
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    | +----+-------------+-------------+-------+---------------+---------+---------+--‌​----+-------+--------------------------+ 
| 1  | SIMPLE      | user_access | index | NULL          | PRIMARY | 38      | NULL | 57224 | Using where; Using index |
VMai
  • 10,156
  • 9
  • 25
  • 34
user3096277
  • 107
  • 8
  • I meant to say a pretty vanilla query. – user3096277 Aug 13 '14 at 20:38
  • What environment are you trying this query on? Is the table being actively updated at the time of execution? Have you tried using a (NOLOCK) – mungea05 Aug 13 '14 at 20:45
  • No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu Utopic Unicorn (development branch) Release: 14.10 Codename: utopic – user3096277 Aug 13 '14 at 20:48
  • 1
    Is your user_name column the first part of your compound index or is it the second one. If it's the second, then MySQL can't use an index and this query will be slow. – VMai Aug 13 '14 at 20:49
  • Have a look at http://stackoverflow.com/questions/10347193/what-does-it-mean-when-mysql-is-in-the-state-sending-data too. – VMai Aug 13 '14 at 20:49
  • mysql version Server version: 5.5.38-0ubuntu1 (Ubuntu) – user3096277 Aug 13 '14 at 20:50
  • I tried to achieve nolock with the suggestion in http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql, but it still hangs – user3096277 Aug 13 '14 at 20:58
  • @VMai Here is explain output. It appears to me that it is using the index +----+-------------+-------------+-------+---------------+---------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+-------+--------------------------+ | 1 | SIMPLE | user_access | index | NULL | PRIMARY | 38 | NULL | 57224 | Using where; Using index | – user3096277 Aug 13 '14 at 21:07
  • Maybe your table or the index file is corrupt. Try [CHECK TABLE](https://dev.mysql.com/doc/refman/5.6/en/check-table.html). In case of errors can eventually [REPAIR TABLE](https://dev.mysql.com/doc/refman/5.6/en/repair-table.html) help. – VMai Aug 13 '14 at 21:15
  • Please provide `SHOW CREATE TABLE user_access;` Without it we are guessing. Your EXPLAIN says that you do not have any index _starting_ with `user_name`, but you do have an index with that field later. Scanning 57K rows takes time, (but it should not take 677+ seconds). – Rick James Feb 18 '15 at 02:31
  • I don't have the job anymore, can't access the data. Thanks. – user3096277 Feb 19 '15 at 15:22

0 Answers0