1

Let's have a simple InnoDB table questions containing one column text, which contains following data:

What color does the sun have?
What year it is?
What year was Barack Obama born?
Where in europe people speak french?
When stackoverflow started?

Now, I would like to search through this column:

SELECT *
FROM `questions`
WHERE `text` LIKE '%What%' OR `text` LIKE '%year%';

However this generate output:

What color does the sun have?
What year it is?
What year was Barack Obama born?

I would like the output ordered by the occurence of searched words. In another words when question contains both "What" and "year" it should precedes questions that contain "What" only. So the output would look like this:

What year it is?
What year was Barack Obama born?
What color does the sun have?

Can that be done using MySQL only? If not is there a nice way of doing this using PHP?

tsusanka
  • 4,801
  • 7
  • 36
  • 42
  • There are multiple ways of achieving this. But the biggest issue is going to be performance. If your records increase, this technique is going to be terribly slow. But if it's just a few thousand records, it's ok. If you wanted the easiest way out, I'd say go for mysql's full-text search – itsols Sep 15 '13 at 16:16
  • There will be around 10 thousand records, nothing too big. Are there full text options even in InnoDB? I don't why but I thought there aren't. – tsusanka Sep 15 '13 at 16:27

1 Answers1

4

The simplest way is to add the following order by to the query:

order by (`text` LIKE '%What%') + (`text` LIKE '%year%') desc

If performance is an issue, then you should look into the MySQL full text functions. They significantly speed up many full text searches.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thx, about the fulltext, I would have to use MyISAM table for that right? – tsusanka Sep 15 '13 at 16:35
  • I would recommend that you build an index on the columns. Then you can use either MyISAM or InnoDB. – Gordon Linoff Sep 15 '13 at 16:40
  • I have an index on the columns. However from the MySQL manual http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html : "Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.". – tsusanka Sep 15 '13 at 18:25
  • @aGR . . . That restriction has been lifted on more recent versions of MySQL. – Gordon Linoff Sep 15 '13 at 20:28