What mysql table structure is better [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 6 years ago.

Improve this 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 want to 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.

---

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.

---

Option 1 would likely perform better.

In option 1, you will be able to read all the data with almost entirely, if not entirely, sequential reads.

In option 2, the rows may not be stored sequentially. If you write them all at the same time, however, then they may actually have good data locality on disk. So, it's hard to know for sure without testing your exact use case.

The best strategy is affected if you are doing incremental updates to your cache table. In option 1, the update will take longer, since the blob may need to be written to a new page. In option 2, you just add new rows, though you might also need to delete rows, too. If you add new rows in incremental updates, you will eventually likely end up with more random reads, which makes reading the cache table slower.

If the word column is the primary key and you are using a recent version of MySQL, you may even get better read performance with InnoDB than MyISAM. With InnoDB, all of the data is clustered with the primary index, so you can retrieve all the data with a sequential read. The fact that you have a blob, though may mean one or more random reads, though. Of course, data that is read often enough to stay in the InnoDB buffer pool won't incur disk reads.

With MyISAM, MySQL needs to read the index table (though it may be cached in a keybuffer) to get pointers into the data table (which may be cached in an OS disk buffer).