MySQL Forums
Forum List  »  Data Warehouse

Re: Large Database Question
Posted by: Rick James
Date: February 13, 2010 03:38PM

FULLTEXT on book title, author, volume title -- well, that could be done with a UNION of FULLTEXT against books and against volumes. And it would probably run faster because there would be fewer rows involved.

book size, or volume size -- There is very little hope for making a useful index on things like this. What will they ask? "Limit to books under 100 pages"? Even if you had the best possibly index, it might ignore the index and simply do a table scan.

Yes there is some overhead for the JOIN. Let's look at some cases:

* You do some big, costly, search on one table, whittle down the list to 10 books, then need to do 10 JOINs to get the details -- The overhead of the JOIN is probably less than the savings of having made the search more compact.

* You are filtering on two tables. This usually kills JOIN because first it needs to filter on one table, then reach into the other table to do the other filter. And it needs to 'reach' (JOIN) thousands of rows. How about presenting some realistic examples where this might happen. Well, even if all the fields were in one table, it still might do the filtering in this clumsy way. In particular, if one filter involves FULLTEXT, it will be done first, without considering the other filter.

How big is all the data? How big is RAM? If everything can be cached in RAM, you will be CPU-bound. If it is too big for RAM, you will be I/O bound -- this is 10x slower. If, by normalizing, you can shrink the size from bigger than RAM to smaller, then you will get some of that 10x, even if the queries have to work harder.

(And database purist would say "normalization" is the 'right' way.)

Options: ReplyQuote

Written By
February 10, 2010 09:30PM
February 11, 2010 10:27PM
February 12, 2010 08:39AM
February 12, 2010 06:23PM
February 12, 2010 06:33PM
February 13, 2010 01:45AM
February 13, 2010 10:40AM
Re: Large Database Question
February 13, 2010 03:38PM

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.