Have Geek Brain

Which mysql table structure is better

I have a very complex search algorithm on my site, so I decided to make a table with a cache or possibly all possible results. I want to ask which structure would be better or maybe not one of them? (MySql)

  • word

    VARCHAR, results

    TEXT or BLOB where I will store the IDs of the found objects (for example, 6 characters for each ID)

  • word

    VARCHAR, result

    INT, but words are no longer unique

I think I will have about 200,000 lines in 1) with 1000-10000 ids each line or 200,000,000+ lines in 2)

The first way takes more memory, but I think it is much faster to find 1 unique string out of 200,000 rather than 1000 strings out of 200 million unique strings

I am thinking of an index on a column word

and without a sphinx.

So what do you think?

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

+2


a source to share


2 answers


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



0


a source


Option 1 will most likely perform better.

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

Option 2 may not store rows sequentially. However, if you write them all at the same time, then they may actually have good data locality on disk. As such, it is difficult to understand for sure without testing your specific use case.



The best strategy will be affected if you are doing incremental updates to the cache table. In option 1, the update will take longer, as it may be necessary to write the frame to a new page. In option 2, you just add new lines, although you may need to delete lines as well. If you add new rows to incremental updates, you will likely end up with more random reads in the long run, which will slow down your cache table reads.

If the word column is the primary key and you are using the latest MySQL version, you can even get better read performance with InnoDB than MyISAM. With InnoDB, all data is clustered with the main index, so you can get all the data in a sequential read. However, the fact that you have a blob though could mean one or more random reads. Of course, data that is read frequently enough to remain in the InnoDB buffer pool will not carry disk reads.

In MyISAM, MySQL has to read the index table (although it can be cached in the key buffer) in order to get pointers to the data table (which can be cached in the OS disk buffer).

0


a source






More articles:


All Articles