>>>>> "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
