MySQL Index Questions

Question

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

Answer 1

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