MySQL and presorting tables

I have a MySQL database on my server with one table with a primary index and fulltext indexing on all other columns. Typically I execute a SELECT statement like:

SELECT * FROM myTable WHERE MATCH(myInfo) AGAINST ('stuff') ORDER BY id LIMIT 30

This is working fine, however it is slow - the ORDER BY id clause is slowing things down considerably, particularly when there are a lot of hits as often happens. For example, without the clause searches take ~0.001 seconds and with the clause 0.6 seconds (but yields the ideal results).

1. Is it possible to presort my table so that I don't ever need the ORDER BY operator? The table is static - the data will only ever be read. After all, having to sort 5000 hits only to return (the top) 30 seems a waste when this can be easily decided in the advance.

2. If not, what can I do about this?

PS - MATCH seems to jumble things up, whereas LIKE although slow does not and so does not need ORDER BY.

%% Edit #1, with output of EXPLAIN SELECT on phpMyAdmin

id  select_type table   type    possible_keys   key key_len ref rows    Extra

1   SIMPLE  myTable fulltext    full_index  full_index  0       1   Using where

Edit #2, better EXPLAIN

indicates time is spent sorting the results.

Status Time

starting 0.000016

checking query cache for query 0.000048

Opening tables 0.000012

System lock 0.000007

Table lock 0.000024

开发者_C百科

init 0.000026

optimizing 0.000010

statistics 0.000017

preparing 0.000012

FULLTEXT initialization 0.000199

executing 0.000004

Sorting result 0.001663

Sending data 0.000304

end 0.000005

query end 0.000004

freeing items 0.000025

storing result in query cache 0.000007

logging slow query 0.000003

cleaning up 0.000005

---

1-Is it possible to presort my table?

No, the table is already presorted by the PK.
The fulltext index forces the rows to be accessed in the order dictated by that index.
For this reason they need to be reordered afterwards.

2- what can I do about this?

If you don't mind getting slightly different results, you can change the query to

  SELECT * FROM 
    (
    SELECT * FROM myTable WHERE MATCH(myInfo) AGAINST ('stuff') LIMIT 30
    ) as s ORDER BY id 

To get the next 30 result do limit 30,30 etc.

You can also speed up the query by not selecting all rows, but only the ones you need. This will limit the amount of data MySQL has to keep in memory and thus the amount of data that has to be moved around while sorting.

SELECT id, myinfo FROM mytable ....

---

Try this:

SELECT * FROM myTable WHERE MATCH(myInfo) AGAINST ('stuff') > 0.25 ORDER BY id LIMIT 30

MATCH(...) AGAINST(...) returns a match score in the range [0,1] (also called "relevancy"). You can prune intermediate results by filtering for low-relevancy rows (the > 0.25 in the query above; if you don't specify this in the WHERE clause, it's the same as specifying > 0). The 0.25 is arbitrary, try to find a good balance between query time and false negatives.

note: I can't guarantee that you'll get the very same results in all cases, but I really don't see how else it could be done.