Jump to content

I am noticing major load time in retrieving results from large tables


imgrooot
 Share

Recommended Posts

I am retrieving data from multiple joined tables that contain around 3,500 rows each. I am only showing limited results but the load time is the same. Like it takes 5 seconds to load the page. I don't want this same issue to continue if I am predicting over 1,000,000 rows from these tables.  So what's the best way to fix this?

 

Here is my query.

$get_members = $db->prepare("SELECT users.*, approvals.*, sponsors.* FROM approvals
LEFT JOIN users ON approvals.user_id = users.user_id
LEFT JOIN sponsors ON approvals.user_id = sponsors.sponsored_user
WHERE user_id > :user_id ORDER BY user_id DESC LIMIT 20");
$get_members->bindValue(':user_id', 0);
$get_members->execute();
$result_members = $get_members->fetchAll(PDO::FETCH_ASSOC);
if(count($result_members) > 0) {
  foreach($result_members as $row) {
    // show results here
  }
} else {
   echo 'none';
}
if(count($result_members) < 20) {} else {
  // show-more button here
}
  • Like 1
Link to comment
Share on other sites

 

  • Don't use user.* etc. Specify just the columns you actually need.
  • Left JOINS are slow. Unless you really need them, use INNER JOIN instead.
  • Make sure you have indexes specified on approvals.user_id and sponsors.sponsored_user

 

 

Should * never be used even if I am retrieving all the columns in that table?

 

So I edited the query using all three of your points above. The load time has decreased from 5 seconds down to 1-2 seconds. It's not instantaneous but much better.

Link to comment
Share on other sites

 

Should * never be used even if I am retrieving all the columns in that table?

Only use * when testing when needing to see/show what is in a table, not in production.

 

If, in future, you add more columns to the table (perhaps a large text field) then all queries using * will pick up the new columns even though not required. The more data your query returns the slower it will run.

 

When joining tables you will get duplicated values (in your query you get the user_id three times.

 

Plus, using * obfuscates the query process. When you come back to it in a few months, or someone else does, the presence of actual column names helps to document what the query is doing.

Link to comment
Share on other sites

WHERE user_id > :user_id

$get_members->bindValue(':user_id', 0);

 

 

i'm wondering why you are doing this comparison? you should not have any data with a zero or negative user_id stored in any table, so the above is causing unnecessary work examining the values and will produce the same result if that comparison is not in the query.

Link to comment
Share on other sites

i'm wondering why you are doing this comparison? you should not have any data with a zero or negative user_id stored in any table, so the above is causing unnecessary work examining the values and will produce the same result if that comparison is not in the query.

 

Use case (statistics) I can think of: you're doing tracking of "logged-in" as well as "not-logged-in" users.  We have some tables like that.

Link to comment
Share on other sites

  • 2 weeks later...

Should * never be used even if I am retrieving all the columns in that table?

You can [manually] use "select *" for testing and diagnostics. 

Applications should never do so. 

 

Databases are intrinsically shared entities and other people might be changing the table structures, adding all sorts of gigantic "text" fields that your application doesn't [know or] care about but using "select *" will retrieve them all anyway. 

 

Regards, 

   Phill  W.

Link to comment
Share on other sites

You can [manually] use "select *" for testing and diagnostics. 

Applications should never do so. 

 

Databases are intrinsically shared entities and other people might be changing the table structures, adding all sorts of gigantic "text" fields that your application doesn't [know or] care about but using "select *" will retrieve them all anyway.

 

Another reason is that the data should be "agnostic" to how it is being used. When working in PHP, and many languages, you can reference the data via the column names. However, what if there was a need to reference the data via numerical index (there's a reason why there are explicit options to only retrieve data via numerical indexes). You might be passing the results to another process that can't use the column names and will simply reference the data by the order it is presented.

 

In that case, if "SELECT *" is used and field order is changed or fields are added, the functionality could break. By selecting just the fields that are needed in the order that they are needed, the functionality will not break (unless someone was to remove a field).

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.