CODEWITHSUNDEEP
mysqlsearch
Andrei V
Andrei V

Reputation: 1538

whats faster search in mysql: table with one column or many smaller columns

Im trying to build a table for search on consolidated data from many different tables. All columns are strings. I'm not sure whats faster way:

  1. Combine all strings into one column (each string separated by |) like "94045|Mountain View|blah|..."
  2. Make table with multiple String columns.

I plan to do "LIKE" search. For example "select id from SEARCH where col1 like '%view%'"

Is there any difference in terms of search speed between 2 approaches?

Upvotes: 0

Views: 410

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53880

I'm assuming that id is your primary key and you're using InnoDB.

If the combined strings are less than the InnoDB single column index limit of 767 bytes, then combine the strings into one column so that MySQL can scan a single, flat index. Then, create a secondary index on that single column.

Because of the wildcard in the prefix, MySQL will still have to scan the index rather than doing a binary search to locate the ids, but scanning an index is faster than scanning the much larger data pages.

In addition, given enough working room (innodb_buffer_pool_size), MySQL will have the index in memory, so it won't have to hit the disk at all.

SELECT id FROM table1
WHERE column1 LIKE '%search%'

Note that the single column index acts as a covering index here because InnoDB secondary indexes always have the primary key also. So, selecting only id, the primary key, against a secondary index, treats it as a covering index.

Upvotes: 1

wallyk
wallyk

Reputation: 57804

If the search pattern has a wildcard before the first character, there is no way to index the data effectively for a fast search, so a "full data scan" is needed.

You can greatly improve the performance by reducing the number of records scanned. Like inspect the data to pick out the zip code and do an indexed search for it (combined with the mad LIKE condition).

Upvotes: 1

galusben
galusben

Reputation: 6402

If each separated word has a different meaning, then separated columns should be better. For instance, if your data is composed from: zip code, street and description, then you should have 3 different columns for that.

This way, you can construct your search to look only on certain fields, if you know the search term should only be in one of the fields.

Upvotes: 1

Related Questions