There is a query $query=mysqli_query($connect, "SELECT * FROM lvl_base WHERE steam LIKE '$rank'"); which is subsequently processed. 5 such requests on one page in a table with a size of 7 thousand. up to 15 thousand. records, load a blank page for 8-15 seconds. How to optimize a query?

  • 2
    What is in $ rank? Is there an index on the steam field and why requests 5, but not one - Mike

2 answers 2

  1. Use indexes .
  2. In your case, steam LIKE = '$rank' equivalent to the expression steam = '$rank' . Operator = compares the entire line, and operator LIKE literal each character, therefore, in general, I advise in this case to use the operator = .
  3. Apparently, 5 times you have a query just with different values ​​of $rank , so it is better in this case to perform 1 query instead of 5 using the IN operator, for example: WHERE steam IN ('$rank1', '$rank2', '$rank3', ...) or using the OR operator, for example: WHERE (steam = '$rank1') OR (steam = '$rank2') OR (steam = '$rank3') OR ...
  4. Do not use SELECT * FROM ... for selecting, maybe you have big data in the database in each row. Use to select only the required fields, for example SELECT id, name ... FROM ...
  5. Use LIMIT ... OFFSET ... - limit the selection, make it page by page
  6. The size of the table in your case is not too large, 7-15 thousand is subjectively small, most likely the brakes arise somewhere else.

Check your code :)

  • one
    2) Why did you decide that there are no wildcard characters in $rank ? No, it is most likely that way, but in general this item looks strange. - Akina
  • @Akina in this particular case, most likely without the % symbols, but yes, if there are, then only LIKE - Evgeny
  • Create an index for the steam column.
  • Do not 5 requests, but 1
  • Use limit
  • Select not all columns, but only the ones you need: select (column1, column2) ,

PS Screen special characters in the $rank variable

  • Yes thank you. But the increase did not give (believing Google) $ rank - STEAM_ID: 0: 1: 737283949562. 5 requests for each database. Different player stats - Sasha Stokolos
  • @ Sasha Stokolos need to understand. maybe it's not the query - Nick
  • The error was in js. Thanks for the advice, but apparently there is nowhere to optimize. 7-15 thousand in each base - this is probably not enough - Sasha Stokolos