Re: Union Performance Question

[Eric Bergen]

Use one big table. A merge table will run the same query over all 10 
tables. The key buffer is filled from the top down so if you have a key 
buffer that looks like this:
               a
              /  \
             /    \
           b      c
          /  \    /  \
        d    e f    g

Almost all queries for that index will be able to use the buffer for 'a' 
and 'b'. It's not until they get things very narrowed down that you have 
to actually hit the index files for specific leaves. Creating ten small 
tables creates ten duplicate 'a' and 'b' sections which polutes the key 
buffer.

-Eric
Dathan Pattishall wrote:
Use 10 smaller tables and perform a union. It's faster to look up in
smaller tables then larger ones-generally. Additionally more of the
key_buffer can be used for tables with the most hits over the tables
with less hits, making the lookup sustain speed over time.

 

-----Original Message-----
From: Dan Salzer [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 11, 2005 11:46 AM
To: mysql@lists.mysql.com
Subject: Union Performance Question

Hi everyone,
I have a question regarding the performance of UNION queries:
I need to do a full-text search against a large number of 
rows. Is it faster to have one table with 10,000,000 text 
rows and perform one full-text search. Or, am I better off 
having 10 smaller more managable tables and performing 
UNION'ed full-text searches against them.

A merge table seems like it would be the ideal solution, but 
the limitation on full-text indexing prohibits it.

Thanks!
-Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]

   

 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]