Paul a écrit,
I'm still showing some problems with the searching speed with the latest
CVS ... maybe there are still some index problems. I tried an author
search on "o'brian, patrick" and the result took about 10-15 seconds to
return. Directly from mysql I get:
mysql> select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word
as m1,marc_word as m2,marc_word as m3 where
biblio.biblionumber=marc_biblio.biblionumber and
biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and
(m1.bibid=m2.bibid and m1.bibid=m3.bibid) and ((m1.word like 'o%' and
m1.tagsubfield in ('100a','110a', '700a', '710a'))and (m2.word like 'brian%' and
m2.tagsubfield in('100a','110a', '700a', '710a'))and (m3.word like 'patrick%' and
m3.tagsubfield in('100a','110a', '700a', '710a'))) order by biblio.title;
77 rows in set (5.34 sec)
here's the explain on that query:
mysql> explain select distinct m1.bibid from
biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_word as m3
where biblio.biblionumber=marc_biblio.biblionumber and
biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and
(m1.bibid=m2.bibid and m1.bibid=m3.bibid) and ((m1.word like 'o%' and
m1.tagsubfield in ('100a','110a', '700a', '710a'))and (m2.word like 'brian%' and
m2.tagsubfield in('100a','110a', '700a', '710a'))and (m3.word like 'patrick%' and
m3.tagsubfield in('100a','110a', '700a', '710a'))) order by biblio.title;
+-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+
| m2 | range | bibid,word,Search_Marc | Search_Marc | 259 | NULL
| 366 | Using where; Using temporary; Using filesort |
| m1 | ref | bibid,word,Search_Marc | bibid | 8 |
m2.bibid | 56 | Using where |
| marc_biblio | eq_ref | PRIMARY,biblionumber | PRIMARY | 8 |
m1.bibid | 1 | Using where; Distinct |
| biblio | eq_ref | PRIMARY,blbnoidx | PRIMARY | 4 |
marc_biblio.biblionumber | 1 | Distinct |
| biblioitems | ref | bibnoidx | bibnoidx | 4 |
biblio.biblionumber | 12 | Using index; Distinct |
| m3 | ref | bibid,word,Search_Marc | bibid | 8 |
m1.bibid | 20 | Using where; Distinct |
+-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+
6 rows in set (0.04 sec)
mysql>
So it still looks like we're using temp and filesort--which I assume is
causing the hangup ... if that is the best we can do we may need to start
thinking about breaking up marc_word into sections (e.g., marc_word_title;
marc_word_author, etc.). The search is really accurate but just too slow
for a production database as large as ours. What do folks think, would that
speed things up?