Thanks for your input. Especially Tod for breaking it down at the hard ware
level and making me realize what is possible.
I have re-evaluated how this is going to work. Instead of trying this with
one query, I am using 2. I forget somethimes that MySQL is better at
running simple queries really fast than complex single queries.
I am now using Full-Text indexing (people will just have to upgrade) to
retrieve any id of a row matching my search criteria eg.
select id from single_table where MATCH (body,author,subject) AGAINST
('search criteria')
I then read all those id in from the results. That takes .2 seconds on my
server. I then feed those results (current 4450 rows) into a second query.
select id from single_table where approved='Y' and id in ( big id list )
order by datestamp desc
That query takes .2 seconds. id is the primary key so look ups on it are
really fast. There is a key on approved, datestamp as well.
>From that I have the rows I am looking for. All in .4 seconds. This is
with 1Mil+ rows. The speed is consistent for all search criteria.
Brian Moon
Phorum Dev Team
---------------------------------------------------------------------
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
