what mysql table structure is better

Question

I have very complicated search algorithm on my site, so i decided to make a table with cache or maybe all possible results. I wanna ask what structure would be better, or maybe not the one of them? (mySQL)

1) word VARCHAR, results TEXT or BLOB where i'll store ids of found objects (for example 6 chars for each id)

2) word VARCHAR, result INT, but words are not unique now

i think i'll have about 200 000 rows in 1) with 1000-10000 ids each row or 200 000 000+ rows in 2)

First way takes more storage memory but i think it would be much faster to find 1 unique row among 200 000, than 1000 rows among 200 mln non unique rows

i think about index on word column and no sphinx.

So that do YOU think?

p.s. as always, sorry for my english if it's not very good.

Answer 1

MyISAM seems to be the default table I see most people use, and I have never personally had a situation where MyISAM didn't work well. This site and this site both list the benefits of each table type available in MySQL if you want something different.

Answer 2

One of the design principals of databases is that with a proper index the number of rows in your table doesn't really matter. So I am not quite sure why you need a cache table.

On the other hand, if you are dealing with full text searches, yes, they do tend to slow down as you have more records in your table (there by contradicting my previous statement :-) )

If slow full text search is indeed the problem, you are probably better of using Sphinx.