MySQL Query Optimization to get montly report

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?


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


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%">
 while($row = mysqli_fetch_assoc($res_users)) {
 $user_id = $row['user_id'];
 $user_name = $row['user_name'];
      <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; } ?>
            // --------------------------------------------------------------------------------------------
            // ETC ETC
            // --------------------------------------------------------------------------------------------
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

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

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

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

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