Slow query 100% cpu

2

I have a problem when I run the page it gets slow because the query reaches 100% CPU.

SELECT id, nombre, precio FROM skinShop WHERE descripcion='premium' AND 
nombre NOT IN (SELECT item_id FROM compras WHERE user_id = '$id_s') ORDER 
BY precio + 0  ASC, nombre ASC LIMIT

This is the complete script: paging:

if(isset($_POST["page"])){
    $page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH); //filter number
    if(!is_numeric($page_number)){die('Invalid page number!');} //incase of invalid page number
}else{
    $page_number = 1; //if there's no page number, set it to 1
}
//get total number of records from database for pagination
$results = $mysqli->query("SELECT COUNT(*) FROM skinShop WHERE descripcion='premium' AND nombre NOT IN (SELECT item_id FROM compras WHERE user_id = '$id_s') ORDER BY precio + 0  ASC, nombre ASC");
$get_total_rows = $results->fetch_row(); //hold total records in variable
//break records into pages
$total_pages = ceil($get_total_rows[0]/$item_per_page);
//get starting position to fetch the records
$page_position = (($page_number-1) * $item_per_page);
//Limit our results within a specified range. 
$results = $mysqli->prepare("SELECT id, nombre, precio FROM skinShop WHERE descripcion='premium' AND nombre NOT IN (SELECT item_id FROM compras WHERE user_id = '$id_s') ORDER BY precio + 0  ASC, nombre ASC LIMIT $page_position, $item_per_page");
$results->execute(); //Execute prepared Query
$results->bind_result($id, $nombre, $precio); //bind variables to prepared statement
//Display records fetched from database.
echo '<div class="row" style="display: -webkit-inline-box;">';

What can I do because I really have days to find a solution? I hope you give me a hand. Thanks

    
asked by kazaf Us 22.09.2018 в 22:22
source

0 answers