CODEWITHSUNDEEP
mysql
user424370
user424370

Reputation: 109

Need help rewriting slow mysql queries

I have 2 slow MySQL queries:

SELECT count(N) as c, mW as move, move.fenW as fen FROM move,opening,book  WHERE move.fenW LIKE BINARY ‘S’  AND move.oid=opening.id AND opening.bid=book.bid AND book.is_deleted=N AND opening.deleted=N AND mW!=‘S’ AND book.published=N GROUP BY mW ORDER BY c DESC

and 

SELECT count(N) as c, mB as move, move.fenB as fen FROM move,opening,book  WHERE move.fenB LIKE BINARY ‘S’  AND move.oid=opening.id AND opening.bid=book.bid AND book.is_deleted=N AND opening.deleted=N AND mB!=‘S’ AND book.published=N GROUP BY mB ORDER BY c DESC

The queries are causing significant delay in website speed loading. I was looking for an advice on how to improve/rewrite these queries to improve website's loading speed and performance.

Upvotes: 0

Views: 27

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

your like operator will probably slowdown this query. lets filter your table first before joining other tables.

SELECT count(N) as c
    , m.mB as move
    , m.fenB as fen 
FROM 
    (SELECT mB, fenB, oid FROM move WHERE m.fenB LIKE BINARY ‘S’ AND mB!=‘S’) m
INNER JOIN opening o on m.oid = o.id AND o.deleted=N    
INNER JOIN book  b on o.bid=b.bid AND b.is_deleted=N  AND b.published=N 
GROUP BY m.mB 
ORDER BY c DESC

Upvotes: 1

Related Questions