-1

These queries appear in our slow query log and we suspect these may be contributing to a high CPU consumption. Any insight on how to optimize them?

Query 1

Takes title of article and list of keywords and tries to match against similar entries

# Query_time: 2.103713  Lock_time: 0.045125 Rows_sent: 12  Rows_examined: 38450
SET timestamp=1645104904;
SELECT
  VID, title, duration, addtime, rate, ratedby, viewnumber, 
  type, thumb, thumbs,
  MATCH(title) AGAINST ("Title of article and list of keywords" 
                        IN BOOLEAN MODE) AS relevance
FROM video
WHERE MATCH (keyword) AGAINST ("Title of article and list of  keywords" 
                               IN BOOLEAN MODE)
  AND active = "1"
  AND channel = "19"
  AND VID <> 40885
  AND VID > 12200
  AND rate >= 3.5
ORDER BY relevance DESC
LIMIT 12;

Explain enter image description here

Query 2

# Query_time: 2.248775  Lock_time: 0.000104 Rows_sent: 21  Rows_examined: 241356
SET timestamp=1645105175;
SELECT s.UID, u.username FROM video_subscribe AS s, signup AS u WHERE s.SUID = 224378 AND s.UID = u.UID;

Explain

enter image description here

SHOW VARIABLES LIKE '%ft%'

ft_boolean_syntax
+ -><()~*:""&|
ft_max_word_len
84
ft_min_word_len
4
ft_query_expansion_limit
20
ft_stopword_file
(built-in)

Type MyISAM

48,312 rows in 'video'

241,350 rows in 'signup'

enter image description here

master00
  • 141
  • 2
  • 12
  • To help you with your [tag:query-optimization] question, we to see your table and index definitions, and your EXPLAIN output. Please [read this](https://stackoverflow.com/tags/query-optimization/info), then [edit] your question. – O. Jones Feb 17 '22 at 21:30
  • 2
    You have posted the same info as the previous closed question https://stackoverflow.com/questions/71165126/how-to-optimize-slow-query – Ergest Basha Feb 17 '22 at 21:32
  • Take a look [here](https://stackoverflow.com/questions/71089039/does-setting-a-virtual-column-as-an-index-in-mysql-improve-query-speed) to check what information should you provide – Ergest Basha Feb 17 '22 at 21:35
  • 1
    I'm not sure from that screenshot of that tool that shows your columns, but it looks like you have no indexes. You need indexes. See https://use-the-index-luke.com for good introduction to indexes. – Andy Lester Feb 17 '22 at 21:35
  • I added EXPLAIN. I think it's indexed isn't that a FULLTEXT query? Doesnt that require an index? – master00 Feb 17 '22 at 21:38
  • 1
    No screenshots where you can show text, please. Say `SHOW CREATE TABLE video;` and then cut and paste the output. Same with EXPLAIN. You can indent text four spaces and get it to render as code or console output. – O. Jones Feb 17 '22 at 21:49

1 Answers1

1

Try creating an index on (active, channel, rate, VID).

ALTER TABLE video ADD INDEX active_channel_VID_rate (active, channel, rate, VID);

It should be useful in satisfying this sequence of filter clauses.

  AND active = "1"
  AND channel = "19"
  AND VID <> 40885
  AND VID > 12200
  AND rate >= 3.5

If rate isn't very selective (that is, if a large number of rows have rate > 3.5) then switch the order of rate and VID in the index.

ALTER TABLE video ADD INDEX active_channel_rate (active, channel, VID, rate);

You could try both of these indexes and see which one works better.

And, if you have a single column index on active, drop it after you create one of the indexes I suggest.

For the second query, do

ALTER TABLE signup ADD INDEX (uid);

to avoid the full table scan of that table.

Oh, and switch to using InnoDB if you can.

O. Jones
  • 103,626
  • 17
  • 118
  • 172