Could be query design & indexing issues, also could be MySQL config issues.
Re query design & indexing: run EXPLAIN EXTENDED on the query, and post that result inside BBCode tags, plus the result of Show Create Table for each table referenced by the query.
Re config, how much RAM is there? To give us a config snapshot, run this script, and post the result inside BBCode tags..
set global log_bin_trust_function_creators=1;
drop function if exists gsintval;
delimiter go
create function gsintval( pname varchar(128) ) returns bigint
begin
declare ret bigint;
select variable_value into ret
from information_schema.global_status
where variable_name=pname COLLATE utf8_unicode_ci;
return ret;
end;
go
delimiter ;
drop function if exists gvintval;
delimiter go
create function gvintval( pname varchar(128) ) returns bigint
begin
declare ret bigint;
select variable_value into ret
from information_schema.global_variables
where variable_name=pname COLLATE utf8_unicode_ci;
return ret;
end;
go
delimiter ;
set @timescale='uptime';
select
'Slow queries/sec' as Item,
round( gsintval('slow_queries') / gsintval(@timescale), 2 ) as Rate,
'Find & optimise slow queries' as Suggestions
union
select
'Unindexed queries/sec',
round( gsintval('select_range_check') / gsintval(@timescale), 2 ),
'Find & optimise slow queries'
union
select
'Full table scans/sec',
round( gsintval('select_scan')/gsintval(@timescale), 2 ),
'Find & optimise slow queries'
union
select
'InnoDB buffer pool inefficiency',
round( gsintval('innodb_buffer_pool_reads') /
gsintval('innodb_buffer_pool_read_requests'), 2 ),
'Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM'
union
select
'InnoDB pages read and buffer pool',
round( gsintval('innodb_pages_read') /
gsintval('innodb_buffer_pool_read_requests'), 2 ),
'As above'
union
select
'InnoDB log thrashing',
round( gvintval('innodb_log_file_size') *
gvintval('innodb_log_files_in_group') /
gvintval('innodb_buffer_pool_size'), 2 ),
'Grow log size if < 30 min'
union
select
'Mins between InnoDB log rotation',
round( gsintval('uptime') / 60 *
gvintval('innodb_log_file_size') /
gsintval('Innodb_os_log_written'), 2 ),
'Grow log size if < 30 min, shrink if > 60 min, but see <a href="www.mysqlperformanceblog.com/2012/10/08/measuring-the-amount-of-writes-in-innodb-redo-logs/">here</a>.'
union
select
'MyISAM query cache hits/sec',
round( gsintval('qcache_hits') /
(gsintval('qcache_hits') + gsintval('com_select')), 2 ),
'If high, grow query_cache_size up to 50'
union
select
'MyISAM query cache spills/sec',
round( gsintval('Qcache_lowmem_prunes') /
gsintval(@timescale), 2 ),
'If < 5, try turning query cache off'
union
select
'MyISAM cache hit rate',
round( gsintval('key_read_requests')/gsintval('key_reads'), 2 ),
'If < 10, grow key_buffer_size'
union
select
'MyISAM key read rate',
round( gsintval('key_reads')/gsintval(@timescale), 2 ),
'Should be high when the buffer is full'
union
select
'MyISAM query cache hits/inserts',
round( gsintval('Qcache_hits')/gsintval('Qcache_inserts'), 2 ),
'If < 5, consider disabling the cache'
union
select
'Temp tables created/sec',
round( gsintval('Created_tmp_disk_tables')/gsintval(@timescale), 2 ),
'Grow tmp_table_size, max_heap_size. Use Heap tables in queries'
union
select
'Database changes/connection',
round( gsintval('com_change_db') / gsintval('connections'), 2 ),
'Use db.table syntax'
union
select
'Processes/connection',
round( gsintval('threads_created') / gsintval('connections'), 2 ),
'If high, grow thread_cache_size'
union
select
'Queries/sec',
round( gsintval('queries') / gsintval(@timescale), 2 ),
'Thousands is busy: you may need better hardware'
union
select
'InnoDB buffer reads/sec',
round( ( gsintval('innodb_buffer_pool_reads') +
gsintval('Innodb_buffer_pool_pages_flushed')) /
gsintval(@timescale), 2 ),
'Thousands is busy: you may need better hardware'
union
select
'Files opened/sec',
round( gsintval('opened_files') / gsintval(@timescale), 2 ),
'If > 5, increase table_open_cache' ;