List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 23 1999 9:07pm
Subject:RE: Long selects
View as plain text  
>>>>> "mikep" == mikep  <mikep@stripped> writes:

mikep> Here is another typical case:
mikep> SELECT count(*) FROM gas_alarms, gas_alarm_attributes WHERE
mikep> gas_alarms.internal_id = gas_alarm_attributes.alarm_id AND state = 'closed'
mikep> AND
mikep> timestamp >= FROM_UNIXTIME(938097835) AND timestamp <=
mikep> FROM_UNIXTIME(938101435);

mikep> Count took 53 seconds

mikep> explain output
mikep>  table                             type                    possible_keys
mikep> key        key_len            ref
mikep> Extra
mikep>  gas_alarms                   ref                       PRIMARY,idx2,idx6
mikep> idx6        1                      closed
mikep> 1369
mikep>  gas_alarm_attributes     ref                       idx1,idx4
mikep> idx1         4                     gas_alarms.internal_id
mikep> 15

Hi!

The bottleneck you are hitting is probable mixing queries that take a
long time with insert/updates .

What did the count(*) return ? (Or how much work did MySQL really have 
to do).

Are you sure that the query didn't wait for an insert before it
started to run?  Did you try 'SELECT HIGH_PRIORITY' to avoid write locks?

Some different suggestions:

- Start mysqld with --low-priority-updates
- use INSERT DELAYED
- use INSERT LOW_PRIORITY
- use SELECT HIGH_PRIORITY

- Create summary tables of your data and query the summary tables
  instead of the log tables.

Regards,
Monty



Thread
Long selectsmikep23 Sep
  • Re: Long selectssasha23 Sep
    • RE: Long selectsSteven Roussey24 Sep
      • RE: Long selectsMichael Widenius24 Sep
RE: Long selectsmikep23 Sep
  • RE: Long selectsMichael Widenius24 Sep