Complex MySQL table schema and its perfomance

Question

I wrote this block of code for my mysql table years ago and now with huge mass of website traffic , I feel the need to change and improve it .

SELECT ns.*
    FROM stories AS ns
    WHERE section='news' 
      AND title!='draft'
      AND ( 
          FIND_IN_SET('4', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('5', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('6', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('7', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('8', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('9', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('10', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('11', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('12', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('13', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('14', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('15', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('16', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('17', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('18', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('19', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('20', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('21', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('22', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('23', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('24', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('25', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('26', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('27', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('28', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('29', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('30', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('31', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('32', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('33', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('34', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('35', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('36', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('37', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('38', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('39', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('40', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('41', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('42', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('43', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('44', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('45', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('46', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('47', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('48', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('49', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('50', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('51', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('52', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('53', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('54', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('55', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('56', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('57', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('58', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('59', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('60', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('61', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('62', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('63', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('64', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('65', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('66', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('67', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('68', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('69', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('70', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('71', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('72', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('73', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('74', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('75', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('76', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('77', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('78', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('79', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('80', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('81', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('82', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('83', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('84', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('85', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('86', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('87', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('88', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('89', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('90', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('91', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('92', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('94', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('95', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('96', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('97', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('98', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('99', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('100', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('101', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('102', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('103', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('104', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('105', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('106', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('107', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('108', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('109', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('110', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('111', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('112', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('113', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('114', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('115', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('116', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('117', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('118', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('119', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('120', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('121', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('122', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('123', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('125', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('126', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('127', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('128', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('129', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('130', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('131', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('132', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('133', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('134', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('135', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('136', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('137', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('138', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('139', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('140', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('141', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('142', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('144', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('145', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('146', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('147', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('148', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('149', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('150', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('151', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('152', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('153', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('154', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('155', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('156', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('157', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('158', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('159', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('160', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('161', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('162', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('163', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('164', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('165', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('166', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('167', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('168', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('169', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('170', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('171', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('172', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('173', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('174', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('175', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('176', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('177', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('178', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('179', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('180', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('181', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('182', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('183', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('184', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('185', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('186', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('187', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('188', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('189', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('190', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('191', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('192', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('193', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('194', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('195', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('196', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('197', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('198', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('199', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('200', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('201', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('202', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('203', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('204', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('206', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('207', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('208', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('209', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('210', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('211', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('212', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('213', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('214', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('215', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('216', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('217', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('218', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('219', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('220', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('221', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('222', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('223', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('224', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('225', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('226', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('227', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('228', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('229', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('230', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('231', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('232', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('233', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('234', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('235', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('236', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('237', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('238', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('239', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('240', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('241', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('242', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('243', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('244', REPLACE(jjob, '-', ','))
       OR FIND_IN_SET('245', REPLACE(jjob, '-', ','))
   ) AND time <= '2015-11-14 08:30:59'
    ORDER BY ns.hotnews DESC,ns.sid DESC

And here is my table structure :

CREATE TABLE IF NOT EXISTS `stories` (
  `sid` int(11) NOT NULL,
  `catid` int(11) NOT NULL DEFAULT '0',
  `aid` varchar(30) COLLATE utf8_bin NOT NULL,
  `title` text COLLATE utf8_bin,
  `time` datetime DEFAULT NULL,
  `hometext` text COLLATE utf8_bin,
  `bodytext` text COLLATE utf8_bin NOT NULL,
  `hotnews` int(1) NOT NULL DEFAULT '0',
  `tags` varchar(255) COLLATE utf8_bin NOT NULL,
  `approved` tinyint(1) NOT NULL DEFAULT '1',
  `section` varchar(15) COLLATE utf8_bin NOT NULL DEFAULT 'news',
  `rate` int(8) NOT NULL DEFAULT '0',
  `rates_count` int(8) NOT NULL DEFAULT '0',
  `jorganization` int(1) NOT NULL DEFAULT '0',
  `jemployment` int(1) NOT NULL DEFAULT '0',
  `jsex` int(1) NOT NULL DEFAULT '0',
  `jagelimit` int(1) NOT NULL DEFAULT '0',
  `jmarriage` int(1) NOT NULL DEFAULT '0',
  `jduty` int(1) NOT NULL DEFAULT '0',
  `jedu` int(1) NOT NULL DEFAULT '0',
  `jmajor` int(1) NOT NULL DEFAULT '0',
  `jfield` text COLLATE utf8_bin NOT NULL,
  `jjob` text COLLATE utf8_bin NOT NULL,
  `jcity` text COLLATE utf8_bin NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=71693 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `stories`
--
ALTER TABLE `stories`
  ADD PRIMARY KEY (`sid`), ADD KEY `tags` (`tags`), ADD KEY `rate` (`rate`), ADD KEY `rates_count` (`rates_count`);

And when I run the query as you can see it takes more than 2 seconds :

 Showing rows 0 - 24 (27875 total, Query took 2.3042 seconds.) [hotnews: 0 - 0] [sid: 71692 - 71631]

and the explain :

What are you trying to do with this query, the example query is massive? — Jeff K, Nov 14 '15 at 05:18
@JeffK I am trying to make it run faster , I thought indexing column jjob is a good idea , but this column is a text type , so I cannot index it . — Farshad, Nov 14 '15 at 05:28
I just gave up on reformatting the query, so it's definitely far too complex! — Darwin von Corax, Nov 14 '15 at 05:30
@DarwinvonCorax , It saves job ids , in jjob column like 23-23-39-92 and when I want to fetch selective ids i need to find and replace in query . — Farshad, Nov 14 '15 at 05:32
You're saying that the `jjob` column contains a *list* of jobids? I think an intersection table would make a world of difference here. — Darwin von Corax, Nov 14 '15 at 05:44
@DarwinvonCorax, I agree, probably a join table and maybe even a person/users table. Seems like a lot of this could be normalized. — Jeff K, Nov 14 '15 at 05:49
yes there is another table containing job details , but here in this table we only store job ids related to this news . — Farshad, Nov 14 '15 at 05:51
Replace the `jjob` column with an intersection table CREATE TABLE IF NOT EXISTS stories_jobs ( sid int(11) NOT NULL, job_id int(11) NOT NULL, CONSTRAINT pk_stories_jobs PRIMARY KEY (sid, job_id), CONSTRAINT fk_stories FOREIGN KEY sid REFERENCES stories (sid), CONSTRAINT fk_jobs FOREIGN KEY job_id REFERENCES jobs (job_id); — Darwin von Corax, Nov 14 '15 at 06:30

Jeff K · Answer 1 · 2015-11-14T06:31:49.347

1

If you only plan on using at max a certain number of the rows returned, one of the simplest (and most beneficial) things to do is to add a LIMIT

Additionally are a few quick things you can do:

Only select the fields you need
Add a covered index (https://www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/)
Modify / restructure jjob

I'm not exactly sure what jjob is doing, but you might need to separate that into another table. For example maybe you have a separate jjobs table and create a join table (stories_jjobs) to hold each unique story/jjob realtionship.

Another option you might want to try is getting rid of all the FIND_IN_SET and REPLACE and try something like AND (jjob LIKE %-6% OR jjob LIKE %-9-% etc...)

EDIT

After looking more at your structure you probably should separate the above into multiple tables, for your long term benefit. Your database is fairly small so creating a few queries to move the data shouldn't be too painful. From what I can gather the j-columns seem to contain person or user data. Maybe you could make a person or users table, a job table, and a junction table table to join stories and jobs?

edited Nov 14 '15 at 06:31

answered Nov 14 '15 at 05:36

Jeff K

543
2
12

Jeff consider that I can not change jjob to like sentence , cause there is no - behind the first id , like 23-24-44-11 – Farshad Nov 14 '15 at 06:02
@Ghazanfari You could run a quick query to update all the records to have a beginning and trailing dash and modify your application code to do the same. – Jeff K Nov 14 '15 at 06:06
let say we do that , what difference does it make ?! I checked it again with like % and it still takes more than 2 seconds to handle that query . – Farshad Nov 14 '15 at 06:20
@Ghazanfari I figured like would be faster than having to use both `FIND_IN_SET` and `REPLACE`, if you can fit all the jjob data into a varchar, I would make that change and index it, then like should be faster. The best option here really is to restructure your tables that way you will stay performant as you grow. – Jeff K Nov 14 '15 at 06:28
@Ghazanfari do you use **all** the rows that query returns or only a certain number, if you only use a certain number a `LIMIT` would drastically speed you up. Also, an index on your time field would do a lot. – Jeff K Nov 14 '15 at 06:29

score 1 · Answer 2 · answered Nov 14 '15 at 12:16

You have a lousy data structure. All those find_in_set() calls suggest a problem with the data structure. It suggests that you are storing jjob as a delimited list. This complicates your data structure and makes the queries much harder to process. You should fix this.

In addition, your query is returning 27,875 rows. This can take some time, depending on how wide the rows are. So, 2 seconds might not be so bad.

In the meantime, though, appropriate indexes might help. Consider the following index for your query:

stories(section, time, title, jjob)

This covers the where clause and so might have some performance gain.

Another thing to try are regular expressions. You can replace all the find_in_set() calls with:

concat('-', jjob, '-') rlike '-4-|-5-| . . .'

A single regular expression might be faster than the dozens of lines that you currently have. I must emphasize, though, that the right solution is a junction table called something like StoryJobs.

why `concat('-', jjob, '-') rlike`? shouldnt be just `jjob rlike ...` looking to OP request the `- ` are already on the `jjob` string as value separators. — Juan Carlos Oropeza, Nov 15 '15 at 13:18
@JuanCarlosOropeza . . . You need the delimiters on the end to properly match the first and last values. — Gordon Linoff, Nov 15 '15 at 23:36

score 0 · Answer 3 · answered Nov 28 '15 at 04:02

This composite index may help by doing more filtering:

INDEX(section, time)

You should switch to InnoDB.

Doing the REPLACE only once should help:

SELECT
     FROM  
      ( SELECT
                id,  -- I am assuming the `PRIMARY KEY` for `stories` is `id`
                REPLACE(jjob, '-', ',') AS commas
            FROM  stories
            WHERE  section='news'
              AND  title!='draft'
              AND  time <= '2015-11-14 08:30:59' 
      ) AS x
    JOIN  stories AS ns ON ns.id = x.id
    WHERE FIND_IN_SET('4', commas)
      OR  FIND_IN_SET('5', commas)
      OR  FIND_IN_SET('6', commas)
      OR  FIND_IN_SET('7', commas)
      OR  ...
      OR  FIND_IN_SET('245', commas)
    ORDER BY  ns.hotnews DESC, ns.sid DESC

Complex MySQL table schema and its perfomance

3 Answers3