MySQL Forums
Forum List  »  Full-Text Search

Re: Multiple Databases - Search and retrieve.
Posted by: Rick James
Date: July 14, 2011 09:46AM

Very crude estimate: A set of columns totalling an average of 200 characters/row will take about 200 bytes/row for the FULLTEXT index on that set of columns.

Avg row len: 700
400 -> compressed to 130
200 -> add 200 for FULLTEXT
100 -> 100 (unchanged) for other stuff, plus 20? for other index(es)
Totals:
700 -> 350
Use
SHOW TABLE STATUS LIKE 'tbl'
before and after to verify those values.

The FULLTEXT index belongs to the table, which belongs to the database, therefore, it applies toward the 1GB/db limit.

SELECT * FROM (
        ( SELECT ... FROM db1.tbl WHERE MATCH ... LIMIT 15 )
        UNION ALL
        ( SELECT ... FROM db2.tbl WHERE MATCH ... LIMIT 15 )
        UNION ALL
        ( SELECT ... FROM db3.tbl WHERE MATCH ... LIMIT 15 )
              ) LIMIT 15

A LIMIT without an ORDER BY is asking for garbage. If you add ORDER BY to the above example, add it in all 4 places. If you do OFFSET and LIMIT, then do
LIMIT 60 -- inside
LIMIT 45, 15 -- outside
That will get page 4.

Pagination and counting the number of rows both will be slow because you have to run something like:
SELECT 
        ( SELECT COUNT(*) FROM db1.tbl WHERE MATCH ... ) +
        ( SELECT COUNT(*) FROM db2.tbl WHERE MATCH ... ) +
        ( SELECT COUNT(*) FROM db3.tbl WHERE MATCH ... )
That is, you have to do all the heavy lifting just to get a count. I think you will quickly find that providing the count is not worth the sluggishness.

As for fixing the performance you will encounter for page 1234, that has to wait until you can do the query in a single database. Meanwhile, don't let users go past page, say, 5. How often have you paginated past page 5 when using a search engine (etc)? Almost never.

SHOW CREATE TABLE; I may have more space-saving suggestions. A common one is INT -> TINYINT (4 bytes -> 1 byte). A subtle one is DATETIME -> TIMESTAMP (8->4). NULL costs a bit (but you may need it). (A lucky pun.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Multiple Databases - Search and retrieve.
2516
July 14, 2011 09:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.