[Solved] MySQL query causing max connections

EverSQL Database Performance Knowledge Base

MySQL query causing max connections

Database type:

I have the following query running against a MySQL db every 30 seconds:

SELECT message.id FROM message WHERE userto='13689' AND tstampviewed IS NULL AND message.status != 'VOID';

It shows up in my slow query log a lot, but it looks to me like it is optimized as best as can be.

The result of EXPLAIN:

SELECT_TYPE = Simple

TABLE = message

TYPE = ref

POSSIBLE_KEYS = userto,tst,stat

KEY = userto

KEY_LEN = 53

REF = const

ROWS = 1

EXTRA = "Using Where"

The keys userto, tst, and stat are all normal BTREE indexes, one for each of the varchar fields referenced in the whereclause of the query. It is a MyISAM table with 300K rows. Users do write to the table consistently, but reads are much more likely (10/1 ratio of reads to writes). The db server is Windows 2008 Enterprise with a lot of CPU and fast drives.

Over the past month, we keep getting max_connection errors, even though I increased the max_connections from 750 to 1500. Several times a day, it seems like a query hangs (I can't verify this because I don't have access to the process list in real time), and 1500 queries pile up behind it and max out the connections. This then obviously causes a lot of other problems.

The above query is the one that consistenly comes up in the slow query log, even though I thought it was optimized as best as possible. Can anyone tell me otherwise or point me in the right direction to solve this problem?

Thanks in advance.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Use Numeric Column Types For Numeric Values (query line: 6): Referencing a numeric value (e.g. 13689) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
ALTER TABLE `message` ADD INDEX `message_idx_userto_tstampview_status` (`userto`,`tstampviewed`,`status`);
The optimized query:
SELECT
        message.id 
    FROM
        message 
    WHERE
        message.userto = '13689' 
        AND message.tstampviewed IS NULL 
        AND message.status != 'VOID'

Related Articles



* original question posted on StackOverflow here.