Sorting and Selecting in MySQL

Question

PHP

Good afternoon. Perhaps the topic of the question is formulated not quite correctly, but below I will try to clarify.
I select from a table SELECT * FROM history WHERE (`dir` = 1 OR `dir` = 3)and get about 25 entries. Next, I work with each entry, checking what dir is equal to. If =3, one variable is incremented. If dir=1, then I need to find in the same table the latest entry in which dir=3 and the position number is equal to the one that I found with dir=1. (to make it a little clearer, this is a table of receipts / withdrawals from the warehouse. 1 = item issued. 3 = accepted to the warehouse. in this case, I am looking for the price at which the position that I am issuing now came in for the last time).
The query SELECT id, doc_cost FROM history WHERE `posid` = $counthis[posid] AND `dir`=3 ORDER BY id DESC LIMIT 1loads MySQL almost completely.
Therefore, I make a complete selection and already in it I look for the maximum price by the usual condition.

$costs = mysql_query("SELECT id, doc_cost FROM history WHERE `posid` = $counthis[posid] AND `dir`=3");
while ($cost = mysql_fetch_array($costs)){
   if($testid<$cost[id]){
      $testid = $cost[id];
   }
}

Thus, I get the value I need quickly enough (no more than 5 seconds).

The question is why is that? And how is it better? How to optimize?

Answer 1

Most likely, a fullscan is launched because it does not find the correct index. What do EXPLAIN queries say? And this, I hope, $counthis[posid] is escaped before being inserted into the query string, otherwise it looks dangerous.

Answer 2

How about an indiscreet question? How did you manage to misspell the word “choice” like that (I even checked: “r” is “k” on the keyboard, and then comes the Cyrillic “y”)?

it's simple, at first it was a choice . I tried to fix it for the sample , but when I saw it, there was an English layout. And instead of K it turned out R ...

Answer 3

I somehow do not see a terrible crime in your requests.
The first is to build indexes on the fields involved in queries.
Second, since the muscle does not support views normally (or does it already support? - a question in the hall), create an additional table of the type:
pos_id int primary,
pos_last_recived DateTime,
pos_last_price float
And when you receive the history in the warehouse, in addition to write the value to it ( insert on duplicate update )
Then With a simple query (via LEFT JOIN) you can simultaneously receive the last price and date of entry of a position.