MySQL Forums
Forum List  »  Newbie

Re: Slow queries. need adivce on creating indexes
Posted by: Rick James
Date: July 28, 2015 03:34PM

where active='1'
Consider removing inactive games/categories/etc from their tables.

    SELECT  `id` , `title` , `thumbnail` , `description` , `plays`
        FROM  `games`
        INNER JOIN  `featuredgames` ON `games`.`id` = `featuredgames`.`gameid`
        where  active='1'
        ORDER BY  rand()
        LIMIT  50 "
-->
SELECT b.`id` , b.`title` , b.`thumbnail` , b.`description` , b.`plays`
    FROM
        ( SELECT gameid FROM featuredgames ORDER BY RAND() LIMIT 50 ) fg
    JOIN  `games` g  ON g.id = fg.id
        where  g.active='1'
        ORDER BY  rand()
Then see if any of the techniques here will help:
http://mysql.rjweb.org/doc.php/random

        INNER JOIN  categories ON games.category=categories.id
        WHERE  games.active='1'
          AND  categories.id NOT IN (8,19,12,24)
-->
        INNER JOIN  categories ON games.category=categories.id
        WHERE  games.active='1'
          AND  games.category NOT IN (8,19,12,24)
and add
INDEX(active, category)
** If that does not speed it up enough, come back for more.
and add
INDEX(active, category)
If that does not speed it up enough, come back for more help.

        where  active='1'
        order by  plays DESC
INDEX(active, plays)

        where  active='1'
          AND  category = " . $id . "
INDEX(active, category)

More on indexing: http://mysql.rjweb.org/doc.php/index_cookbook_mysql
Convert to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb

Is this 'yes'/'no'? If so, consider ENUM('yes','no'):
`active` varchar(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

What is this? It takes 9 bytes:
`order` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',

Options: ReplyQuote


Subject
Written By
Posted
Re: Slow queries. need adivce on creating indexes
July 28, 2015 03:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.