MySQL Query Optimization to get montly report

66
June 15, 2018, at 11:40 AM

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>
Answer 1

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.

Answer 2

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.

Answer 3

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

READ ALSO
htaccess rewrite a multi-query string into a path

htaccess rewrite a multi-query string into a path

How would i go about changing the query string

59
One mysqli connection for multiple database functions

One mysqli connection for multiple database functions

My main page calls many database functions, stored in the dbphp file

68
How to take a variable inside if else statement in PHP

How to take a variable inside if else statement in PHP

I am trying to take a value inside of if/else statement and send an error message with "that variable" because I don't want any of my inputs to be more than 99 characters

81
Notice: Undefined index: fileToUpload in C:\wamp\www\upload.php on line 46

Notice: Undefined index: fileToUpload in C:\wamp\www\upload.php on line 46

I am trying to upload image files to an AWS S3 server using phpHowever, I keep getting this error

89