How can i optimize this mysql query to be faster when it comes to thousands of rows?

I just started my website, and after 5 days it slowed down, it took nearly 30 seconds to load the website, because the mysql database got bigger with plus 50-60k rows.

I have searched hours for solution, but i did not find anything that i could implement to optimize my query.

$idstack = $_SESSION['ids'];
      $stmt = $mysqli->prepare("SELECT 
      maps.id,
      maps.name,
      maps.date,
      maps.mcversion,
      maps.mapid,
      maps.description,
      maps.java,
      maps.bedrock,
      maps.schematic,
      users.username,
      users.rank,
      users.verified,
      (SELECT COUNT(*) FROM likes WHERE likes.mapid = maps.id) AS likes,
      (SELECT COUNT(*) FROM downloads WHERE downloads.mapid = maps.id) AS downloads,
      (SELECT COUNT(*) FROM views WHERE views.mapid = maps.id) AS views
      FROM maps
      INNER JOIN users 
      ON maps.userid = users.id
      WHERE maps.id NOT IN ( '" . implode( "', '" , (array)$idstack ) . "' ) ORDER BY RAND() DESC LIMIT 15");

      $stmt->execute();

      $result = $stmt->get_result();

This query returns what i want, but it is slow when the database has thousands of rows. Any idea how could i optimize it?

Answer

For your query, you want indexes on:

likes(mapid)
downloads(mapid)
views(mapid)
maps(id, userid)
users(id)

You may already have some of these indexes, if the columns are declared as primary keys.

That said, the query might still be hard to optimize. NOT IN with ORDER BY is going to be tricky.

These are created as:

create index idx_likes_mapid on likes(mapid);

and so on.

Advertisement

Answer