Implementing a database search using a query using

Question

I plan to implement a database search through the website - I know that there is a full-text search offered by mysql, but it turns out that it is not supported by the innodb engine (which I need to support transactions). Other uses for sphinx or similar indexing applications. However, they require some re-factoring of the database structure and may take longer to implement than mine.

So, I decided to include each table and merge all its corresponding columns into the newly added QUERY column. This query column should also be typed from a column of other relevant tables.

This is achieved, I will use the "how" clause in the query column of the table for the search to search for results for specific domains (a group of related tables).

Since my database will not be too large (<1mn rows in the largest table), I expect reasonable query times.

Does anyone agree with this method or has a better idea?

Answer 1

You will not be happy with the decision to use LIKE with wildcards. It works hundreds or thousands of times slower than using full-text search technology.

See my presentation Practical Full-Text Search in MySQL .

, QUERY, MyISAM, FULLTEXT. .

, , .

CREATE TABLE OriginalTable (
  original_id SERIAL PRIMARY KEY,
  author_id INT,
  author_date DATETIME,
  summary TEXT,
  body TEXT
) ENGINE=InnoDB;

CREATE TABLE SearchTable (
  original_id BIGINT UNSIGNED PRIMARY KEY, -- not auto-increment
  -- author_id INT,
  -- author_date DATETIME,
  summary TEXT,
  body TEXT,
  FULLTEXT KEY (summary, body)
) ENGINE=MyISAM;

Answer 2

. , MySQL .

, "equals" (LIKE 'test') " " (LIKE 'test%'), MySQL . , "" (LIKE '%test%') .

" " ('LIKE %test), , LIKE 'test%', .

, , . , .

LIKE. , "", , , "" " " .

LIKE ad-hoc- .

Answer 3

No, this is not optimal, since it forces you to read the entire line. But if the table is small (I don’t know what the value is <1mn), then it may be acceptable to some extent.

In addition, you can limit the search function. For example, some sites restrict the use of the search function to no more than one query per minute, while others you must enter captcha.