2

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

1
  • 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
2

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

1
  • +1. I'm also curious for the original poster to tell us what the speed improvements were.
    – Jonathan
    Sep 16 '11 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.