# Performance problem with \> 1GB table and some queries

**URL:** <https://forums.percona.com/t/performance-problem-with-1gb-table-and-some-queries/145>\
**Category:** Other MySQL® Questions\
**Created:** [December 15, 2006, 7:54am UTC](https://forums.percona.com/t/performance-problem-with-1gb-table-and-some-queries/145 "2006-12-15T07:54:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tzi2k](https://avatars.discourse-cdn.com/v4/letter/t/aca169/32.png) [@tzi2k](https://forums.percona.com/u/tzi2k)\
**Post date:** [December 15, 2006, 7:54am UTC](https://forums.percona.com/t/performance-problem-with-1gb-table-and-some-queries/145/1 "2006-12-15T07:54:42Z")

</div>

Dear Gentlemen,

I have a performance problem with one of the tables of a given DB schema. The schema is not allowed to be modified (except adding index or changing db engines) because it is used by a third party SW which I cannot modify. For reading convenience I have attached a TXT file with the textbody below )

The table is named “result” and is 1.7GB in size.  
The schema looks like this:

±------------------±-------------±-----±----±---------- --------±---------------+  
| Field | Type | Null | Key | Default | Extra |  
±------------------±-------------±-----±----±---------- --------±---------------+  
| id | int(11) | NO | PRI | NULL | auto\_increment |  
| create\_time | int(11) | NO | | | |  
| workunitid | int(11) | NO | MUL | | |  
| server\_state | int(11) | NO | MUL | | |  
| outcome | int(11) | NO | | | |  
| client\_state | int(11) | NO | | | |  
| hostid | int(11) | NO | MUL | | |  
| userid | int(11) | NO | MUL | | |  
| report\_deadline | int(11) | NO | | | |  
| sent\_time | int(11) | NO | | | |  
| received\_time | int(11) | NO | MUL | | |  
| name | varchar(254) | NO | UNI | | |  
| cpu\_time | double | NO | | | |  
| xml\_doc\_in | blob | YES | | NULL | |  
| xml\_doc\_out | blob | YES | | NULL | |  
| stderr\_out | blob | YES | | NULL | |  
| batch | int(11) | NO | | | |  
| file\_delete\_state | int(11) | NO | MUL | | |  
| validate\_state | int(11) | NO | | | |  
| claimed\_credit | double | NO | | | |  
| granted\_credit | double | NO | | | |  
| opaque | double | NO | | | |  
| random | int(11) | NO | | | |  
| app\_version\_num | int(11) | NO | | | |  
| appid | int(11) | NO | MUL | | |  
| exit\_status | int(11) | NO | MUL | | |  
| teamid | int(11) | NO | | | |  
| priority | int(11) | NO | | | |  
| mod\_time | timestamp | NO | | CURRENT\_TIMESTAMP | |  
±------------------±-------------±-----±----±---------- --------±---------------+  
29 rows in set (0.07 sec)

mysql\> show index from result;  
±-------±-----------±------------------±-------------±- -----------------±----------±------------±---------±---- —±-----±-----------±--------+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |  
±-------±-----------±------------------±-------------±- -----------------±----------±------------±---------±---- —±-----±-----------±--------+  
| result | 0 | PRIMARY | 1 | id | A | 172984 | NULL | NULL | | BTREE | |  
| result | 0 | name | 1 | name | A | 172984 | NULL | NULL | | BTREE | |  
| result | 1 | res\_wuid | 1 | workunitid | A | 172984 | NULL | NULL | | BTREE | |  
| result | 1 | ind\_res\_st | 1 | server\_state | A | 3 | NULL | NULL | | BTREE | |  
| result | 1 | ind\_res\_st | 2 | priority | A | 15 | NULL | NULL | | BTREE | |  
| result | 1 | res\_app\_state | 1 | appid | A | 1 | NULL | NULL | | BTREE | |  
| result | 1 | res\_app\_state | 2 | server\_state | A | 3 | NULL | NULL | | BTREE | |  
| result | 1 | res\_filedel | 1 | file\_delete\_state | A | 2 | NULL | NULL | | BTREE | |  
| result | 1 | res\_userid\_id | 1 | userid | A | 1383 | NULL | NULL | | BTREE | |  
| result | 1 | res\_userid\_id | 2 | id | A | 172984 | NULL | NULL | | BTREE | |  
| result | 1 | res\_userid\_val | 1 | userid | A | 1383 | NULL | NULL | | BTREE | |  
| result | 1 | res\_userid\_val | 2 | validate\_state | A | 2084 | NULL | NULL | | BTREE | |  
| result | 1 | res\_hostid\_id | 1 | hostid | A | 2507 | NULL | NULL | | BTREE | |  
| result | 1 | res\_hostid\_id | 2 | id | A | 172984 | NULL | NULL | | BTREE | |  
| result | 1 | res\_wu\_user | 1 | workunitid | A | 172984 | NULL | NULL | | BTREE | |  
| result | 1 | res\_wu\_user | 2 | userid | A | 172984 | NULL | NULL | | BTREE | |  
| result | 1 | idx\_received\_time | 1 | received\_time | A | 10175 | NULL | NULL | | BTREE | |  
| result | 1 | idx\_exit\_status | 1 | exit\_status | A | 13 | NULL | NULL | | BTREE | |  
±-------±-----------±------------------±-------------±- -----------------±----------±------------±---------±---- —±-----±-----------±--------+  
18 rows in set (0.00 sec)

Some more statistics about the particular table:

mysql\> SELECT COUNT(_) FROM result;  
±---------+  
| COUNT(_) |  
±---------+  
| 172984 |  
±---------+  
1 row in set (0.00 sec)

The following queries take quite long:

SELECT  
received\_time as raw\_date,  
DATE\_FORMAT(FROM\_UNIXTIME(received\_time), ‘%d. %M %Y’) AS format\_date,  
count(case when server\_state=5 and outcome=1 then 1 end) as wu\_success,  
sum(cpu\_time/3600) as cpu\_hours,  
count(case when server\_state=5 and outcome!=1 and outcome!=4 then 1 end) as wu\_other  
FROM  
result  
GROUP BY  
format\_date DESC  
ORDER BY  
raw\_date DESC

±—±------------±-------±-----±--------------±-----± --------±-----±-------±--------------------------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±-------±-----±--------------±-----± --------±-----±-------±--------------------------------+  
| 1 | SIMPLE | result | ALL | NULL | NULL | NULL | NULL | 172984 | Using temporary; Using filesort |  
±—±------------±-------±-----±--------------±-----± --------±-----±-------±--------------------------------+

and the second query:

SELECT  
count(case when server\_state=4 then 1 end) as wu\_progress,  
count(case when server\_state\<4 then 1 end) as wu\_unsent  
FROM  
result  
WHERE  
server\_state\<5 and received\_time=0  
±—±------------±-------±-----±----------------------- ------±------------------±--------±------±-------±----- -------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±-------±-----±----------------------- ------±------------------±--------±------±-------±----- -------+  
| 1 | SIMPLE | result | ref | ind\_res\_st,idx\_received\_time | idx\_received\_time | 4 | const | 148234 | Using where |  
±—±------------±-------±-----±----------------------- ------±------------------±--------±------±-------±----- -------+

They both take around 30seconds which is quite a lot for generating realtime statistics )

Any ideas how to tune them?

Mysql:  
Server version: 5.0.30 Gentoo Linux mysql-5.0.30  
using myisam tables (no innodb)  
key\_buffer\_size = 240M

The box is a Pentium4/HT 2.4 GHZ (CPU is only at 6% when running the queries) with 512MB RAM.

TIA,

Thomas

---

<div class="post-metadata">

**Author:** ![carpii](https://avatars.discourse-cdn.com/v4/letter/c/b5ac83/32.png) [@carpii](https://forums.percona.com/u/carpii)\
**Post date:** [December 16, 2006, 12:56am UTC](https://forums.percona.com/t/performance-problem-with-1gb-table-and-some-queries/145/2 "2006-12-16T00:56:29Z")

</div>

2nd query should be easy to tune, just add an index on (server\_state, recieved\_time).

For the first query, its using a temp table. You can try to get rid of this but I dont think youll succeed because of the group by.

I would move the blobs out of this table and into another table. I think this may help