0

I have a fairly large table (News article table) (~ 6 GB) and this table be larger every day.

I want to search in this table based on user parameters set.

My search is based on Category , Agency , Register Time and Words user enter.

When User Search

Words: "مذاکرات هسته ای"

Cat : 2

Agency : 1

Register Time : 1426883460

I Write This Query:

select news.title,news.id,news.agency,news.cat,news.regTime,news.img,news.view,news.like,
newsagency.title as agency_title,
site_cat.title as cat_title
from news 
right join site_cat 
    on news.cat = site_cat.id 
right join newsagency
    on news.agency = newsagency.id 
where 
    news.regTime>=1426883460 and
    news.cat = 2 and
    news.agency =1  and 
    ( 
        (news.title like '%مذاکرات هسته ای%' or news.tag like '%مذاکرات هسته ای%' or news.des like '%مذاکرات هسته ای%') 
        or 
        (
            (news.tag like '%مذاکرات%' or news.tag like '%مذاکرات%' or news.tag like '%مذاكرات%' )  and 
            (news.tag like '%هسته%' or news.tag like '%هسته%' or news.tag like '%ةستة%' ) and 
            (news.tag like '%ای%' or news.tag like '%ای%' or news.tag like '%اي%' )
        ) or 
        (
            (news.title like '%مذاکرات%' or news.title like '%مذاکرات%' or news.title like '%مذاكرات%' ) and 
            (news.title like '%هسته%' or news.title like '%هسته%' or news.title like '%ةستة%' ) and 
            (news.title like '%ای%' or news.title like '%ای%' or news.title like '%اي%' )
        ) or 
        (
            (news.des like '%مذاکرات%' or news.des like '%مذاکرات%' or news.des like '%مذاكرات%' ) and 
            (news.des like '%هسته%' or news.des like '%هسته%' or news.des like '%ةستة%' ) and 
            (news.des like '%ای%' or news.des like '%ای%' or news.des like '%اي%' )
        ) 
    ) 
order by  news.regTime desc 
limit 0,15

This Query Is so Slow in My Table now, and I am sure , it's be slower.

What can I do for faster search?

Update: Table Schema

News Table:

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8_persian_ci NOT NULL,
  `link` varchar(500) COLLATE utf8_persian_ci NOT NULL,
  `des` varchar(500) COLLATE utf8_persian_ci NOT NULL,
  `tag` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  `img` varchar(400) COLLATE utf8_persian_ci NOT NULL,
  `type` int(11) NOT NULL,
  `agency` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  `regTime` int(11) NOT NULL,
  `view` int(11) NOT NULL,
  `like` int(11) NOT NULL,
  `disslike` int(11) NOT NULL,
  `point` int(11) NOT NULL,
  `cat` int(11) NOT NULL,
  `mytag` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  `top` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat` (`cat`),
  KEY `agency` (`agency`),
  FULLTEXT KEY `mytag` (`mytag`),
  FULLTEXT KEY `tag` (`tag`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;

site_cat Table :

CREATE TABLE IF NOT EXISTS `site_cat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_persian_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci

newsagency Table:

CREATE TABLE IF NOT EXISTS `newsagency` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  `link` varchar(500) COLLATE utf8_persian_ci NOT NULL,
  `topLink` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  `sort` int(11) NOT NULL,
  `img` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  `view` int(11) NOT NULL,
  `des` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `story` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `tag` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `imgF` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `url` varchar(250) COLLATE utf8_persian_ci NOT NULL,
  `flag` int(11) NOT NULL,
  `encode` int(1) NOT NULL,
  `persian` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `view` (`view`),
  KEY `sort` (`sort`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
Oveys
  • 113
  • 1
  • 9
  • See **Section2 / What does Show your Schema Mean?** of [What is Sqlfiddle and why should I care?](http://stackoverflow.com/a/38899465) to help us to help you – Drew Oct 22 '16 at 15:52
  • why not do [FTS](http://stackoverflow.com/a/30677347) as you have the indexes set up for such but are not doing FTS . Unless your text-ish column data has the search string as left-most the index won't be used as you have it and it will do full table scans – Drew Oct 22 '16 at 16:02
  • Complete 3 Table Schema Addedd @Drew – Oveys Oct 22 '16 at 16:05
  • I'm Tested It but for persian Words , it's not Good @Drew – Oveys Oct 22 '16 at 16:06
  • poke around at some other answers, http://stackoverflow.com/a/18981221 , and [Other Search](http://stackoverflow.com/search?q=%5Bmysql%5D%5Bfull-text-search%5D+arabic) – Drew Oct 22 '16 at 16:08
  • I saw that , but 1- Indexed columns must <= 1000 byte encoding, and mine is very much more @Drew – Oveys Oct 22 '16 at 16:11
  • consider `TEXT` columns, tell us what version of mysql you have `select @@version` – Drew Oct 22 '16 at 16:14
  • for similarity full text is so good, but for exact search engine , it isn't good enough. i'm using full text for similarity and it's good , but in search result , it's show very irrelevant result @Drew – Oveys Oct 22 '16 at 16:14
  • well that is true about FTS. I use solr. The bottom line is that `LIKE` for mid-string non-left-most is going to table scan – Drew Oct 22 '16 at 16:15
  • Is Solr good for exact search based on multi parameters and Condition ?! – Oveys Oct 22 '16 at 16:20

0 Answers0