help on query/group by

[mel list_php]

Hi,
A friend of mine asked me to have a look at one of his query, and I'm 
stuck....
Here was his query:
SELECT drugID, protID, COUNT(DISTINCT pmid),
      MAX(s1.syn) AS o1, MAX(s2.syn) AS o2
FROM matches
INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%'
INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%'
INNER JOIN sentence ON sentID=id
GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601

and this is his goal:
"The idea is quite simple: The table called 'matches' contains triples
 drugID, protID, sentID
indicating a co-occurence of a drug and a protein in a sentence. The
user of course searches for either drug name or protein name or
both. In the above query, the user wants everything for all drugs
starting with 'a'.
The MAX() calls more or less arbitrarily choose one of the many names
associated with drugID as a representative. With the COUNT() I want to
find out how many different medline abstracts (not sentences) have a
hit."
The matches table is 1,247,508 rows, sentence is  817,255 rows and synonyms 
is 225,497 rows.

First I think using inner join in that case is not helpful, because it is 
making a whole cartesian product on the tables, whereas a left join would 
limit the number of rows.
The second line "INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn 
LIKE '%'"  is useless I think, because it just retrieves the not null values 
for protID.

I also added indexes on the table (i'm not very familiar with indexes, so 
that is probably my problem)
- on matches: index on protID,drugID and sentID
- on sentence: index on id (primary key)
- on synonyms: index on nameID,syn

Here are the tables:
mysql> desc matches;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| protID | text     | YES  | MUL  | NULL   |       |
| drugID | text    | YES  |         | NULL    |       |
| sentID | int(11) | YES  | MUL | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc sentence;
+-------+------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned |        | PRI | NULL    | auto_increment |
| text  | text                  | YES  |     | NULL     |                |
| pmid  | int(11)            | YES  |     | NULL      |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc synonyms;
+--------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| nameID | text | YES  | MUL | NULL    |       |
| syn       | text | YES  |        | NULL    |       |
+--------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
I wanted to see how where used the indexes:
mysql> explain select * from matches left join synonyms on drugID=nameID;
+----------+------+---------------+------+---------+----------------+---------+-------+
| table        | type | possible_keys | key   | key_len   | ref              
    | rows      | Extra |
+----------+------+---------------+------+---------+----------------+---------+-------+
| matches   | ALL  | NULL              | NULL|    NULL | NULL               
| 1247508 |       |
| synonyms | ref   | c                     | c     |      23    | 
matches.drugID |       4      |       |
+----------+------+---------------+------+---------+----------------+---------+-------+
2 rows in set (0.00 sec)

mysql> explain select * from matches left join synonyms on drugID=nameID 
left join sentence on sentID=id;
+----------+--------+---------------+---------+---------+----------------+---------+-------+
| table       | type   | possible_keys | key           | key_len | ref       
            | rows       | Extra |
+----------+--------+---------------+---------+---------+----------------+---------+-------+
| matches  | ALL     | NULL           | NULL       |    NULL   | NULL        
       | 1247508 |       |
| synonyms | ref     | c                  | c             |      23     | 
matches.drugID |       4      |       |
| sentence  | eq_ref | PRIMARY     | PRIMARY |       4     | matches.sentID  
|       1      |       |
+----------+--------+---------------+---------+---------+----------------+---------+-------+
3 rows in set (0.00 sec)

If I do a "reverse" query of the first one, no index is used ( select * from 
synonyms left join matches on drugID=nameID;) what I don't understand: I 
thought syn is indexed, so it will quickly find the ones beginning by 'a%' 
and then do the join with matches but on a limited number of records only, 
so it will be much faster.

I finally manage to have something quite working: (but not the final result 
my friend is hoping!!)

select * from matches left join synonyms on drugID=nameID left join sentence 
on sentID=id limit 0,100;

returns me the result in 0.01sec . The limit here is compulsory or the query 
is once again very slow.

My problem now is then I add the count distinct and the group by it's very 
slow again...

I know that this mail is quite long , and I didn't manage to have a nice 
presentation of the tables, but if somebody could give me advice on this?And 
explain me which indexes I should use and why?

Thanks a lot,
Melanie
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]