Hello everyone i am planing to create table with 10 columns, which should be at least 10,000,000 rows, and inside of it, i would have column description - VARCHAR(600) and index on it.
So the question is, would it be faster to query LIKE on that column - VARCHAR(600), or it would be faster to split the description in 6 columns with 100 characters and indexing them with LIKE and OR..
For example: description - VARCHAR(600) INDEX
SELECT `id` FROM `table` WHERE `description` LIKE "%something%"
or it would be faster to split description in 6 columns like desc1, desc2,..desc6 with VARCHAR(100) INDEX - each of them and use following query:
SELECT `id` FROM `table` WHERE `desc1` LIKE "%something%" OR `desc1` LIKE "%something%" OR `desc3` LIKE "%something%" OR `desc4` LIKE "%something%" OR `desc5` LIKE "%something%" OR `desc6` LIKE "%something%"
Just wondering before i start creating the database..
Best regards and Thanks to everyone!
It makes no difference at all . . . well, very little. The query is going to have to do a full table scan, because the like pattern starts with a wildcard ("%").
If you care about performance, you should probably rephrase the problem as a full-text search. Start with the documentation.