CODEWITHSUNDEEP
phpmysqlquery-optimization
Hid Dencum
Hid Dencum

Reputation: 592

MySQL Query Optimization to get montly report

I need to optimize a query. Script return a simple table result like this below.

I'm like to speed up results, maybe one solution is consolidating montly query into only one.

What is the best and correct way to get the listing with good speed?

TABLE

YAER 2018
ID     NAME    JAN   FEV   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC
00001  PAUL    OK    OK    OK    OK    -     OK    -     -     -     -     -     -   
00002  MARK    OK    OK    OK   -     -    - OK    -     -     -     -     -     -      
00003  JACK    OK    -     -    -     -    - OK    -     -     -     -     -     -   
more 50K lines

My Database is simple

 USERS
  user_id
  user_name

 DOCUMENTS
  document_id
  document_user_id
  document_date
  document_file

First, I get a a users, in this way

$query_users = " SELECT user_id, user_name FROM users ";
$res_users = mysqli_query($mysqli,$query_users) or die(mysqli_error($mysqli));

And create a table with a while

<table style="width:100%">
    <tr>
        <th>ID</th>
            <th>NAME</th>
            <th>JAN</th>
            <th>FEB</th>
            <th>MAR</th>
            <th>APR</th>
            <th>MAY</th>
            <th>JUN</th>
            <th>JUL</th>
            <th>AUO</th>
            <th>SEP</th>
            <th>OCT</th>
            <th>NOV</th>
            <th>DEC</th>
        </tr>

<?php
 while($row = mysqli_fetch_assoc($res_users)) {
 $user_id = $row['user_id'];
 $user_name = $row['user_name'];
?>    
    <tr>
      <td><?php echo $user_name; ?></td>

// For each month I run this query to check if user have document on year
            <? 
            // --------------------------------------------------------------------------------------------
            // GET JAN DOCS
            // --------------------------------------------------------------------------------------------
            $query_doc_month_01 = "
                SELECT document_id, document_date,
                SUM(documento_date) AS total_documents
                FROM documents
                WHERE document_user_id = '$user_id'
                AND document_date = '$year-01-00'
           LIMIT 1
            ";              

            $res_doc_month_01 = mysqli_query($mysqli,$query_doc_month_01) or die(mysqli_error($mysqli));

            while($row_month_01 = mysqli_fetch_assoc($res_doc_month_01)) {
                $document_month_01_id = $row_month_01['document_id'];

            ?>    
            <td <? if ($document_month_01 == '') { echo 'bgcolor="#f1f1c1"'; } ?>>
            <? if ($document_month_id == '') { echo '- - -'; } else { echo $document_month_01_id; } ?>
            </td>
            <?php
                }
            ?>

    <? 
            // --------------------------------------------------------------------------------------------
            // SAME ABOVE TO GET FEB DOCS
            // SAME ABOVE TO GET MAR DOCS
            // ETC ETC
            // --------------------------------------------------------------------------------------------
        ?>
     </tr>
        <?php
            }
            mysqli_close($mysqli);                                  
            ?>

    </table>

Upvotes: 0

Views: 47

Answers (3)

Yosra Hamza
Yosra Hamza

Reputation: 549

Try this:

SELECT user_id, user_name, YEAR(document_date) as year, MONTH(document_date) as month, COUNT(*)
FROM users
LEFT JOIN documents ON (document_user_id = user_id)
GROUP BY user_id, year, month

This should return one row for each user having documents on a specific month of a year, if no documents found nothing will be returned, you can check and display empty data for the non-returned rows

Upvotes: 0

Grenther
Grenther

Reputation: 476

Currently you're doing this: Query with N results for each N results you're performing another query, times twelve.

Which means you're flooding the database with a significant amount of query requests. Use an inner-join. This isn't the complete query but it'll help you get started:

 SELECT u.user_id, u.user_name 
 FROM users U INNER JOIN documents D 
 ON D.document_user_id = u.user_ID

Do this with either one, or at most 12 queries if you must.

Avoid making an insane amount of requests.

Upvotes: 0

Knightsbridge
Knightsbridge

Reputation: 561

Looks like you could use a JOIN on your first query and get all the data at once. This way you wouldn't be doing another query in the loop.

Select user_id, user_name, document_id, document_date, document_file from users u INNER JOIN documents on d.document_user_id = u.user_id order by u.user_id

If you still want to show users with no documents use a left JOIN instead of inner.

By only using one query it should be a lot faster. You the set a var for the user I'd and create a new row when it changes.

Can I also suggest you don't prefix your columns with the table name. It's redundant in my opinion. Just document.file would be enough to know other than document.document_file.

Typing on a phone, sorry if any typos.

Upvotes: 1

Related Questions