We are having very serious performance issues (the
client is angry, very angry) with full-text searches
that return a large number of results. The problem is
roughly as follows:
we have a 2-million record database of books, a bit of
a mess, each record is a mass of unstructured data.
A search thus:
SELECT * FROM book WHERE MATCH(author) AGAINST('john')
LIMIT 100;
takes 5 seconds - too long.
SELECT * FROM book WHERE MATCH(author)
AGAINST('trollope') LIMIT 100;
takes 0.04 seconds - perfect.
Both return 100 results, but the first does so much
too slowly. The only difference between the two
searches is that:
SELECT COUNT(*) FROM book WHERE MATCH(author)
AGAINST('john');
gives
81291 (in 52.71 seconds)
whereas
SELECT COUNT(*) FROM book WHERE MATCH(author)
AGAINST('trollope');
gives
794 (and takes 0.82 seconds)
In other words, popular search terms cause severe
database slowdown, so much that we're currently
installng MS SQL in an attempt to resolve these
issues. But is there anything we can do with MySQL?
All we want is for something like
SELECT * FROM book WHERE MATCH(author) AGAINST
('stephen') AND MATCH(author) AGAINST ('king') LIMIT
100 /* these queries are ALWAYS limited like this */,
a pretty normal kind of query, to return in an
acceptable fast time; SELECT * FROM book WHERE
MATCH(author) AGAINST (trollope') LIMIT 100 already
does.
It's certainly possible in some products - whether in
MySQL or not is something I'd really like the answer to.
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music
Charts
http://uk.my.yahoo.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
