Building inverted indexing system with MySQL

Question

I am building a mobile service that require a lot of search queries.

The service is based on MySQL data system, and search query is not enough to make a fast search service.

Therefore, I decided to use inverted indexing system:

index    Documents
1        a, b, c, d, e, f, g, h
2        c, k, i, j, k

This is a simple construction for the inverted indexing system.

I assume that there will be more than thousand documents for one row.

I am not sure what kind of type and length that I should use for the 'Documents' column?

I selected VARCHAR(100000) for now. Is it possible to set the length like 9999999?

Answer 1

1. Data structure:

   index document
     1      a
     1      b
     1      c
    ...
     2      c
     2      k
   

   index type INT, document type CHAR(1). Primary key as set of index and document.

   Queries will perform very fast with this data structure.
   By the way, the structure I propose is normalized.

2. From MySQL 5.0 Reference Manual (The CHAR and VARCHAR Types):

[...] VARCHAR [...] The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

Answer 2

IMHO, this will get ugly, but that being said, your varchar size would cover it, or you could look into TEXT, mediumTEXT, or longTEXT:

TEXT    65,535 bytes    ~64kb
MEDIUMTEXT   16,777,215 bytes   ~16MB
LONGTEXT    4,294,967,295 bytes ~4GB