Mysql above 5mil lines

Asked

Viewed 62 times

0

Good people I am developing a code only that I came across the following problem, when I performed performance test with 5,000 lines he exceeded the limit of 30 sec... what can be wrong?

$app->get('/receitagrafico/', function() use ($app) {
$request = \Slim\Slim::getInstance()->request();
 $router = $app->router();

$datai = date("Y-m-01");
$dataf = date("Y-m-t");

    $stmt1 = getConn()->query("SELECT SUM(totalz + entrega - ajuste) AS valor_total_soma FROM movimento WHERE status!='C' AND data BETWEEN '{$datai}' AND '{$dataf}'");
        $onoff = ( $stmt1->rowCount() > 0 ? true : false );
        $dados_produto = $stmt1->fetchObject();
        $total_atual = ( $onoff ? $dados_produto->valor_total_soma : 0 );

    $stmt1 = getConn()->query("SELECT SUM(totalz + entrega - ajuste) AS valor_total_soma FROM movimento WHERE status!='C' AND data BETWEEN '{$datai}' AND '{$dataf}'");
        $onoff = ( $stmt1->rowCount() > 0 ? true : false );
        $dados_produto = $stmt1->fetchObject();
        $bugfix = ( $dados_produto->valor_total_soma == 0 ? 1 : $dados_produto->valor_total_soma );
        $total_passado = ( $onoff ? ($total_atual-$dados_produto->valor_total_soma)/$bugfix*100 : 0 );

$total_lanche = [];
$lanche_lengd = [];
    $stmt1 = getConn()->query("SELECT produtos,data FROM movimento WHERE status!='C' AND data BETWEEN '{$datai}' AND '{$dataf}'");
        $onoff = ( $stmt1->rowCount() > 0 ? true : false );
        while($dados_produto = $stmt1->fetchObject()){
            if ($stmt1->rowCount() > 0) {
                $arr = explode(',', $dados_produto->produtos);
                $arrN_am = array();
                foreach($arr as $item){
                    $valor = explode(':', $item);
                    $arrN_am[][$valor[0]] = $valor[1];
                }
                foreach($arrN_am as $item => $id){
                    foreach($id as $item2 => $id2){
                        $id_produto = $item2;
                        $qtd = $id2;
                        if(is_double($id2)){
                            $qtd = 1;
                        }
                        $stmt3 = getConn()->query("SELECT * FROM produto WHERE hash={$item2}");
                        $resultado_lanche = $stmt3->fetchObject();
                        if (!isset($total_lanche[$resultado_lanche->nome])) {
                            $total_lanche[$resultado_lanche->nome] = $qtd;
                            $lanche_lengd[] = $resultado_lanche->nome;
                        }else{
                            $total_lanche[$resultado_lanche->nome] += $qtd;
                        }
                    }
                }
            }
        }

        if ($onoff){
            $tt3 = $lanche_lengd;
            $tt4 = [];
            foreach($total_lanche as $item => $id){
                $tt4['name'] = 'Abril';
                $tt4['data'][] = $id;
            }
        }else{
            $tt3 = 0;
            $tt4 = false;
        }

echo "[";
echoResponse(200, $total_atual);
echo ",";
echoResponse(200, $total_passado);
echo ",";
echoResponse(200, $tt3);
echo ",";
echoResponse(200, [$tt4]);
echo "]";

});

Return:

[53,0,["Torrada","Cachorro quente"],[{"name":"Abril","data":[4,1]}]]

1st edition: I made the changes in the variables, db for DATE, and in the code, I did not put the date as index. I’ve already run a test with 5,000 lines in this new code and it’s still exceeding time

bd structure: inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

new comparison... it worked 5mil querys

        $stmt3 = getConn()->query("SELECT hash,nome FROM produto");
        $resultado_lanche = $stmt3->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
        $resultado_lanche = array_map('reset', $resultado_lanche);
        $resultado_lanche = array_map('reset', $resultado_lanche);

$total_lanche = [];
$lanche_lengd = [];
    $stmt1 = getConn()->query("SELECT produtos,data FROM movimento WHERE status!='C' AND data BETWEEN '{$datai}' AND '{$dataf}'");
        $onoff = ( $stmt1->rowCount() > 0 ? true : false );
        while($dados_produto = $stmt1->fetchObject()){
            if ($stmt1->rowCount() > 0) {
                $arr = explode(',', $dados_produto->produtos);
                $arrN_am = array();
                foreach($arr as $item){
                    $valor = explode(':', $item);
                    $arrN_am[][$valor[0]] = $valor[1];
                }
                foreach($arrN_am as $item => $id){
                    foreach($id as $item2 => $id2){
                        $id_produto = $item2;
                        $qtd = $id2;
                        if(is_double($id2)){
                            $qtd = 1;
                        }

                        if (!isset($total_lanche[$resultado_lanche[$item2]])) {
                            $total_lanche[$resultado_lanche[$item2]] = $qtd;
                            $lanche_lengd[] = $resultado_lanche[$item2];
                        }else{
                            $total_lanche[$resultado_lanche[$item2]] += $qtd;
                        }
                    }
                }
            }
        }
mysql mariadb
  • 1

    Execute your queries with a EXPLAIN at the front (just before the SELECT) and post the results here. Only then will it be possible to give an accurate opinion. It will probably be necessary to create indexes in your tables. Saving dates with the proper type instead of string should help as well.

  • Type: Errorexception Code: 8 Message: Undefined Property: stdClass::$totalz File: C: xampp htdocs pHpServer-Adm api v1 index.php Line: 4528

  • 2

    The date storage gambiarra is the biggest problem (maybe not the only one). It already has the date format field for a reason. Date is formatted only at the time of display, not in the database. The first thing to do is to convert table columns to Date or Datetime.

  • blz I will adjust this date field, plus some critical problem or some hint to rewrite?

  • Try this first, and see what’s changed. I’m reading the rest to see if there’s anything else. Remember that you will have to fix the PHP side to pass the date in YYYY-MM-DD no between

  • It’s easy, just convert the field (hint: create a datetime field, and do not touch the original yet, give an UPDATE SET table new field = STR_TO_DATE.... old field) and then change $id = "01/".$dfaxxx256.$dfa43; for $id = $dfa43.'-'.$dfaxxx256.'-01';

  • If you later [Edit] the post and better format your code indentation, it helps to read as well.

  • 1

    Select will look something like this: WHERE data BETWEEN '$id' AND '$id2'

  • In fact, it doesn’t even need 2 variables. You can put the days in the select itself: WHERE data BETWEEN '{$anomes-01}' AND '{$anomes-31}' - Could also use WHERE MONTH(data) = $mes AND YEAR(data) = $ano, simply, but there avails index. Each case is a case.

  • 2

    Have one more caution - Mysql accepts no between illegal dates like January 31st, but does not validate in the insertion. It would be better to review the logic of 31, taking the last day of the month with PHP - There is an answer to this on the site: https://answall.com/questions/38632/70

  • @Bacco already updated as much as I could

  • Well, now with the better indented code, you can see that you are doing another query within the loop of the first one. The problem is hardly in Mysql now, but in your loop. You need to rethink all this logic to make only two queries. For example, make the first one to get all the hashes, and then make another one to get the products. Other than that, instead of SELECT *, you can just grab the columns you’re actually going to use. To be honest, rewriting the code on second thought is less work than trying to fix this logic. There is a lot of complication.

  • Even the first query, I did not understand the reason to do it twice. And there is so much foreach inside the other that it is even difficult to follow. Probably wouldn’t need all that. It would be easier if you explained the structure of the tables, and the result you want to get (with an example of what should be returned), instead of showing the way you think it will solve.

  • following the first takes the total of the current month, the second takes the total of the last month, and makes the growth rate. the third query organizes everything in name/quantity

  • @Bacco I found here... and the logic of the third querry... now as I simplify this analysis

  • So, but you’re doing query inside loop. Better separate, make one of each, and use groupings and Joins. Try to simplify steps, forget what’s been done so far and rethink how to do everything with fewer steps

  • 1

    Example: instead of making a SELECT for each "hash", you can group all the hashes in an array, and select .... WHERE hash IN ( hash list) only

  • @Bacco blz I’ll see if I can. I’ll put a fetchall out of the querry and put this Assay in to see if it works... correct?

  • @Bacco so is 2 search the same way? ex. SELECT * FROM sector WHERE id NOT IN (SELECT id_sector FROM employees GROUP BY id_sector);

  • @Bacco I managed to do otherwise... but it worked with 5 thousand querys... Help me clean this code and how do you do with this IN... Pow wanted to learn... or I have to modulate the bd products and modification? what is the best option

  • @Bacco Eae what is the diagnosis of this 3rd query?

Show 16 more comments

1 answer

0

How are you doing a query that uses BETWEEN in a table with many records, the field you use in the between must be set to INDEX.

Mysql (and other database engines) will optimize searches that filter through these fields.

  • 1

    Index will not solve, because it is not doing between in the column, but in a conversion with STR_TO_DATE. Before the index it needs to set the column to date (see comments above)

  • Yes, I agree the field must be date first of all. But even though he was using the STR_TO_DATE, the field can be date already, as it is not specified in the question what kind it is, I assume it is and there the INDEX will definitely help.

  • @Bacco puts your suggestion to create new field and use the between without doing the conversion as an answer so it can be voted and accepted. I think that’s the right solution here.

  • I didn’t even post as answer yet because there may be other problems, only really tried to help in what I saw that clearly is complicated. Anyway, let’s see if the author tests and gives a feedback, then we can complement.

Browser other questions tagged mysql mariadb

You are not signed in. Login or sign up in order to post.