Re: Slow queries. need adivce on creating indexes
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',