Plesk Question posted in
The official documentation for Plesk Obsidian can be found here.

How can I make my database response time & render time better? – Plesk

The company I work for has an in-house administrative side to our site. I’ve noticed its running alot slower and I’m assuming its not because the side of our database but the way we are making the calls and rendering them out to the screen. I’m going to list out an example of our process to render stuff out and hopefully someone can tell me where we are doing it inefficiently to help speed up render loading times.

//Student Page:
Avg. Loading Time: 5 - 6 seconds

//table_user: 
Columns   Records
45        16,412

//table_use_course: 
Columns    Records
22         18,623

//Indexing:
Comment: We use plesk (phpmyadmin) and this is where I did the indexing.
I indexed all primary and foreign keys and it looks like this:

enter image description here

//Query: 
SELECT a.account_id, user_id, user_first_name, user_last_name 
FROM table_user a 
INNER JOIN table_user_course b ON a.account_id = b.account_id 
WHERE user_active = 'yes' AND b.product_type_id = '1' 
ORDER BY user_first_name ASC LIMIT 0, 30


//Query Results:
Columns    Records
4          30


//How we render:
if(is_array($query_results)){
    foreach($querY_results as $student){
         $text .= $student['user_first_name'], $student['user_last_name'], etc.
    }
}

Additional Thoughts:

We do have a test site with the same code and database structure, just a lot less data in the db. The test site runs much quicker which makes me think its how we pull it from the database. On the other hand 16k – 18k records isn’t that much and I’m surprised and the difference in loading time between the test site and the live site.

Any help or suggestions on how to make things run quicker would be much appreciated!

Tags:

2

Answers


  1. 0

    While indexing, one need to focus in the columns used in the inner part… in your case:

    table_user.account_id
table_user_course.account_id

    In both tables. Try that and clarify if you have already done

    Example: 

    ALTER TABLE `TABLE_NAME` ADD INDEX `INDEX_NAME` (`COLUMN_NAME`)

    Source: How do I add indices to MySQL tables?

    For your case, use:

    SHOW INDEX FROM [TABLE_NAME]; (For checking the indexes in your tables)

    And add (only if they don’t already exist):

    ALTER TABLE `table_user` ADD INDEX `INDEX_NAME` (`account_id`);
ALTER TABLE `table_user_course` ADD INDEX `INDEX_NAME` (`account_id`);
    Login or Signup to reply.
  2. 0

    Perhaps the performance issue isn’t on the database. Trace the page execution with http://www.xdebug.org it will tell, for each line in source, execution time and memory usage.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search