Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 MyISAM tables:

table: search_hit with primary key: search_hit_id, and foreign key: search_id (150 million rows and growing with constant inserts) table: search with primary key: search_id (about 30,000 rows and growing)

search_hit table has a number of indices,including search_id, but this query is excruciatingly slow:

select sq.search_id, count(ssh.search_hit_id)
from search sq
inner join search_hit ssh 
use index(by_search_id) on sq.search_id = ssh.search_id
where sq.search_id >= 47000
group by sq.search_id

here is the explain results:

1, 'SIMPLE', 'sq', 'range', 'PRIMARY', 'PRIMARY', '4', '', 202, 'Using where; Using index'
1, 'SIMPLE', 'ssh', 'ref', 'by_search_id', 'by_search_id', '4', 'lims.sq.search_id', 3358, ''

query returns with status like: /* 0 rows affected, 1,575 rows found. Duration for 1 query: 3.198 sec. (+ 5.975 sec. network) */

Our server setup MySQL 5.0.45 on 64 bit linux with 8GB dual core (hyperthreaded quancore). Would dropping the index, optimize table then re-create the index help here, or anything?

Thanks

David

share|improve this question
    
Please post the results of running the query with EXPLAIN at the beginning and use index(by_search_id) removed –  fuscata Sep 16 '11 at 16:47
add comment

migrated from serverfault.com Sep 16 '11 at 17:29

This question came from our site for professional system and network administrators.

1 Answer

You must refactor the query to perform WHERE, JOIN and GROUP BY clauses IN THAT EXACT ORDER !!!

Here is the refactored query :

select
    sq.search_id,
    count(1) search_id_count
from
    (select search_id from search where search_id > 47000) sq
    inner join
    (select search_id from search_hit where search_id > 47000) ssh
    using (search_id)
group by
    sq.search_id;

Running OPTIMIZE TABLE (reduces table fragmentation and recreate indexes) may not be necessary unless you do heavy INSERTs, UPDATEs, DELETEs. I'll say it is optional.

Give it a Try !!!

share|improve this answer
    
+1. I'm also curious for the original poster to tell us what the speed improvements were. –  Jonathan Sep 16 '11 at 22:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.