0

I have a big table with over a two million items.

I have a SQL selection to get items selected by some WHERE informations but my call is not really fast.

Is there a way to get faster call?

That is my call now

SELECT id, name, price, pricebefore, link, imagelink, updated, site, siteid 
        FROM items 
        WHERE (case when pricebefore3 is NULL then pricebefore else pricebefore3*1.5 end) >= pricebefore 
        AND price < pricebefore 
        AND isbn != -1 
        AND 1 = CASE
          WHEN (100-price/pricebefore*100) > 90 THEN updated < NOW() - INTERVAL ".$timeago." MINUTE
          ELSE (100-price/pricebefore*100) > ".mysqli_real_escape_string($link, $percentage)." 
        END
        ".preg_quote($merchant)."
        ORDER BY updated DESC 
        LIMIT ".mysqli_real_escape_string($link, $perPage)." 
        OFFSET ".mysqli_real_escape_string($link, $site);

The id is primary in the table.

All others like name, siteid, price and everything is index

I tried much more SQL calls but i dont find any faster way... any ideas to make the SQL Call faster? The Table is InnoDB with utf8mb4_0900_ai_ci

The SQL call checks where the price is lower than before. I have a crawler that get prices from sites and changed it to pricebefore when get new one. The "Price" is the current price, so it shows the item when the price is lower than pricebefore

All other WHERe is just for time interval or isbn = -1 if i dont want to show it and something.

In merchant i put a regex to find just a few shops and not all sites

tevved
  • 25
  • 2
  • 2
    Edit your question and provide sample data, desired results, and a clear explanation of what the query should be doing. – Gordon Linoff Jun 13 '21 at 11:39
  • 2
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Jun 13 '21 at 11:40
  • What is your definition of: "... not very fast"? You will likely need to append the question with the output of `explain` of that query. Beware the previously mentioned comments about SQL injection issues. – Paul T. Jun 14 '21 at 01:32
  • @PaulT. When i add "AND link REGEXP" when someone look for just a merchant, than its much slower than only with percentage where. So when multiple WHEREs, its much slower... loke 3-4 times lower than before. There is no SQL Injection cause its variable in CODE not from users browser – tevved Jun 14 '21 at 08:59
  • Ok, on the no injection. What about the explain? Add the word `explain` before the `select`. The output from that explain is what we need to see. – Paul T. Jun 16 '21 at 01:39

0 Answers0