Task #829
hlstats_Events_Chat needs some indexes
| Status: | Closed | Start date: | 11/18/2009 | |
|---|---|---|---|---|
| Priority: | Normal | Due date: | ||
| Assignee: |  agent86 |
% Done: | 100% |
|
| Category: | Web | |||
| Target version: | 1.6.3 | |||
| Blocking Target Version: |
Description
We do not have any indexes on playerId and serverId and we are using this in a join for the Chat pages. Need to create two seperate indexes.
chat_optimization.patch
(2.5 kB)
Associated revisions
[svn r1509] Added two new indexes on hlstats_Events_Chat, fixes (#829)
[svn r1509] Added two new indexes on hlstats_Events_Chat, fixes (#829)
History
#1
Updated by Packhead over 2 years ago
- % Done changed from 0 to 60
Added the indexes in r1509. The "all servers" page is still really slow to generate. 6+ seconds compared to the .09 for individual server pages.
#2
Updated by agent86 over 2 years ago
hlstats_Servers could use an index on 'game'... probably won't help much but it will help a bit.
Otherwise, what stands out is the fact that the query is done twice - once for the results page and once for the total count of results. That can be optimized using a mysql SQL extension, (like... sql_count_rows or something in the select statement) although the name escapes me. Will investigate further.
#3
Updated by Packhead over 2 years ago
- Assignee changed from Packhead to agent86
#4
Updated by Packhead over 2 years ago
Agent86 is providing a .patch for the chat page to clean up some of the SQL.
#5
Updated by agent86 over 2 years ago
- File chat_optimization.patch added
Try this and see if the performance improves.
I added a new function to class_db.php to expose the 'last calculated rows' and added an option (default false) to the query function so that you can invoke the SQL_CALC_FOUND_ROWS functionality of mySQL. It will modify your query to incorporate the proper keyword and cache the resulting found_rows value.
I have to cache it in case we've got profiling on - the found_rows() funciton only operates on the last query, which would be the profiling query if profiling was turned on, sigh.
#6
Updated by Packhead over 2 years ago
- Status changed from Assigned to Closed
- % Done changed from 60 to 100
Applied patch from agent86 on r1519.