New answers tagged

1

I'm no huge fan of Microsoft Access, but I'd be curious to know why you were unsuccessful at it. It should easily be able to hold that information and is probably easier to learn than other RDBMS systems. Having said that, I think either MySql or Sql Server Express would work great. You would use a text import process (Sql Server has a wizard that makes it ...


3

Assuming that multiple "words" are separated by a space character. It's not clear whether you want a compare total counts across the whole table or counts per row. for the whole table: SELECT word, count(*) AS ct FROM tbl, unnest(string_to_array(message, ' ')) word -- implicit LATERAL join GROUP BY 1 ORDER BY 2 DESC LIMIT 100; -- top 100 Or, ...


2

MySQL is completely capable of serving as your full text search engine. InnoDB FTS indexes are reasonably good. With the size you described it should all fit in memory but of course that depends on the other tenants on the shared hosting. If it's not available (you mentioned shared hosting) you can implement your own pretty easily. Full text search is just ...


1

I often query a 170K-row table using InnoDB FULLTEXT. (Avg: 300 words.) I typically get the answer of a few dozen rows in under 200ms. That 200ms drops to below 20ms if the cache is warm. I don't think the number of documents in the table matters as much as the number of documents returned. If you need to fetch the text for 100 documents (after ...


0

I think the best solution (assuming we do not the substring lenght) is as follows: Create a temp table with three columns, two columns are the PKs of table_1 and table_2, and the 3rd column is the concatenation of the two string columns from table_1 and table_2, and then we search this new table for the result we need. -- create temp tables create table #s ...


1

34 tables is a lot. Over-nomalization Many-to-many mappings that should be 1:many Excessive use of "added by" Use minimal datatypes Some examples: Because of the nested nature of districts-cities-states, and the fact that city and state names "never" change, there is no need to carry that normalization past district (or maybe even area). I suggest it ...


2

Lets start with comparing between MyISAM and InnoDB: Overview MyISAM: Faster than InnoDB since the structure is simpler and take less resources. It support full text indexing better than InnoDB. Good for read-intensive table. Simplicity InnoDB: Data integrity and foreign key constraints. InnoDB is more resistant to table corruption than MyISAM. ...


2

It works. It performs reasonably well. You have two tables, one is InnoDB and contains most of the attributes of an entity. The other is MyISAM, is 1:1 with the first table, and contains a TEXT field, plus FULLTEXT index. The relevant query looks something like: SELECT ... FROM inno_tbl i JOIN ft_tbl f ON i.id = f.id WHERE i.stuff... AND ...



Top 50 recent answers are included