Jump to content
imgrooot

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

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

Share this post


Link to post
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

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

When optimizing queries, you need to be running EXPLAIN on them. If you provide the output of the Explain here, then people might be able to pinpoint ways you can make the query faster.

  • Like 1

Share this post


Link to post
Share on other sites

 

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

 

 

Right.  3500 x 3500 = 12.25M rows in/of itself, and let's not think about the possibility of multiplying the result set size again.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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).

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.