1

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!

Dom
  • 858
  • 2
  • 11
  • 30
  • 1
    You need to provide more information, as Charles Rojas said, but you ALSO need to use 2 things; ANALYZE and EXPLAIN. ANALYZE will help the query planner to make better choices and EXPLAIN will tell you how the querys are beign built. You can then, add indexes to the columns you really need to be indexed. – Solrac Nov 05 '14 at 22:52
  • You're asking for "user_active = 'yes'" - have you tried to put an index on this? And if it can be just yes or no you should use at least tinyint (0 = no, 1 = yes) or boolean for better indexing - also note: 16-18k records are nothing. My company got an application with like 1.000.000 records and the select + joins do fine – kair Nov 06 '14 at 09:20
  • The structure of the db is not up to me. I use tiny ints for bool on my own projects and I wouldn't have made a table named "table_user" and have the primary be "account_id". – Dom Nov 06 '14 at 20:54

2 Answers2

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`);
Community
  • 1
  • 1
Gerardo Charles Rojas Vega
  • 633
  • 7
  • 16
0

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

Niloct
  • 9,491
  • 3
  • 44
  • 57