Greetings, I am struggling trying to get good performance
using indexes.
I'll spare the details and narrow down the situation. I have about
100,000 rows of data with the following fields:
title - vachar(64)
description - text
state - varchar(2)
priority - int
modified - date
People type in a search string, and I need the results ordered in
the following way:
1) by priority DESC
2) items matching the title
3) items matching the description
I've experimented with full-text search, but cannot get it to do
this effeciently all in one query because of the way I need
to order things. I tried adding two full-text indexes like:
SELECT title, MATCH(title) AGAINST ('query') AS intitle FROM mytable WHERE
MATCH(title, description) AGAINST ('query') [AND state = 'state'] ORDER by priority
desc, intitle desc, modified desc;
but this only uses the (title,description) index, an not the (title)
index or the state index.
It would be much faster if I could do only a full text search on
records with the state that is selected. (Any ideas on how to do this?)
Currently I do the following which which is really weak when there is
no state selected:
SELECT title, (title LIKE '%query%') AS intitle FROM mytable WHERE ((description LIKE
'%query%') OR (title LIKE '%query%')) [AND state='state'] ORDER BY priority desc,
intitle desc, modified desc;
Does anyone have any better ideas? I would consider doing multiple queries
using the different indexes but then I don't know how many rows matched
without taking out the duplicates which would be expensive, I think.
Thanks in advance,
John
---------------------------------------------------------------------
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
