Table search how to do?

Question

PHP

Hi all. Please tell me how to make a quick relevant search in a table with a large number of rows. There is a table:

CREATE TABLE `sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand` varchar(255) DEFAULT NULL,
  `gname` varchar(255) DEFAULT NULL,
  `sku` varchar(255) DEFAULT NULL,
  `price` varchar(255) DEFAULT NULL,
  `description` text,
  `tagdescription` varchar(255) DEFAULT NULL,
  `tagtitle` varchar(255) DEFAULT NULL,
  `tagkeywords` varchar(255) DEFAULT NULL,
  `adddate` datetime DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `photos` text,
  `url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `url` (`url`) USING BTREE,
  FULLTEXT KEY `brand` (`brand`),
  FULLTEXT KEY `sku` (`sku`),
  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `tagdescription` (`tagdescription`),
  FULLTEXT KEY `tagtitle` (`tagtitle`),
  FULLTEXT KEY `tagkeywords` (`tagkeywords`),
  FULLTEXT KEY `price` (`price`),
  FULLTEXT KEY `gname` (`gname`)
) ENGINE=MyISAM AUTO_INCREMENT=1734271 DEFAULT CHARSET=utf8;

I do a php search in it like this:

$result = mysql_query("SELECT *, MATCH brand AGAINST ('".$_GET['q']."') + MATCH gname AGAINST ('".$_GET['q']."') + MATCH description AGAINST ('".$_GET['q']."') + MATCH tagdescription AGAINST ('".$_GET['q']."') as relev FROM sale HAVING relev>0 ORDER BY relev DESC");

With 1,734,271 records, the search takes 4.1535229682922 seconds, which is a very long time. How to do it quickly?

Answer 1

on some field value like xxx'); drop table sale -- your search will end.

Answer 2

And how about making a search_entity table in which to put:
`id` int(11) NOT NULL AUTO_INCREMENT,
`sale_id` int(11) NOT NULL,
`text` text,
And FULLTEXT KEY `text` (`text`)
And make a record 1d1 into the given field.
Moreover, text is all fields separated by a space, it is possible without punctuation marks, unions, etc.
Then the search will be on a separate table, you can make your own ranking (for example, add the sort key) and select the necessary fields.

Answer 3

Be sure to make injection protection. You should never and never directly insert data from $_GET, $_POST and others into SQL queries without any checks ( https://habrahabr.ru/post/148701/).
On the question - look towards Elastic Search or Sphinx. On one project, Sphinx significantly speeds up the search

Answer 4

Do a search through sphinx