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

[imgrooot]

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
}

[Barand]

• 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

[imgrooot]

 

• 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.

[Barand]

 

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.

[mac_gyver]

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.

[gizmola]

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.

[dalecosp]

 

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.

[dalecosp]

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.

[Phi11W]

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.

[Psycho]

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).