List:General Discussion« Previous MessageNext Message »
From:Alexandre Vieira Date:September 3 2010 9:44am
Subject:Re: Performance problems on MySQL
View as plain text  
Hi Johnny,

mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type  | possible_keys | key     | key_len
| ref   | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | clientinfo | const | PRIMARY       | PRIMARY | 23
| const |    1 |       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.53 sec)

Thanks

BR
AJ

On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers <johnny@stripped> wrote:

> What about an explain of this query:
>
>
> SELECT * FROM clientinfo WHERE userid='182106617';
>
> -JW
>
>
> On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira <nullpt@stripped> wrote:
>
>> John, Johnny,
>>
>> Thanks for the prompt answer.
>>
>> mysql> SHOW CREATE TABLE clientinfo;
>>
>>
> +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>> | Table      | Create
>> Table
>> |
>>
>>
> +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>> | clientinfo | CREATE TABLE `clientinfo` (
>>   `userid` varchar(21) NOT NULL default '',
>>   `units` float default NULL,
>>   `date_last_query` datetime default NULL,
>>   `last_acc` int(10) unsigned default NULL,
>>   `date_last_units` datetime default NULL,
>>   `notification` int(10) unsigned NOT NULL default '0',
>>   `package` char(1) default NULL,
>>   `user_type` varchar(5) default NULL,
>>   PRIMARY KEY  (`userid`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>>
>>
> +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>> 1 row in set (0.00 sec)
>> mysql> SHOW INDEX FROM clientinfo;
>>
>>
> +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>> | Table      | Non_unique | Key_name | Seq_in_index | Column_name |
>> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>>
>>
> +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>> | clientinfo |          0 | PRIMARY  |            1 | userid      |
>> A         |      460056 |     NULL | NULL   |      | BTREE      |         |
>>
>>
> +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>> 1 row in set (0.00 sec)
>>
>>
>> SELECT * FROM clientinfo WHERE userid='182106617';
>>
>> UPDATE clientinfo SET
>>
> units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
>> WHERE userid='152633876';
>>
>> INSERT INTO clientinfo VALUES
>> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
>> units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
>> notification=0, package='D', user_type='PRE';
>>
>> DELETE FROM clientinfo WHERE units='155618918';
>>
>> There are no other type of queries.
>>
>> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
>>
>> We also run some other applications in the server, but nothing that
>> consumes all the CPU/Memory. The machine has almost 1GB of free memory and
>> 50% of idle CPU time at any time.
>>
>> TIA
>>
>> BR
>> Alex
>>
>>
>> --
>> Alexandre Vieira - nullpt@stripped
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
>



-- 
Alexandre Vieira - nullpt@stripped

Thread
Performance problems on MySQLAlexandre Vieira2 Sep
  • Re: Performance problems on MySQLJangita2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
      • Re: Performance problems on MySQLJangita3 Sep
  • RE: Performance problems on MySQLTravis Ard2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
Re: Performance problems on MySQLAlexandre Vieira3 Sep
  • Re: Performance problems on MySQLJohnny Withers3 Sep
    • Re: Performance problems on MySQLAlexandre Vieira3 Sep
      • Re: Performance problems on MySQLJohnny Withers3 Sep
        • Re: Performance problems on MySQLAlexandre Vieira3 Sep
          • Re: Performance problems on MySQLJohnny Withers3 Sep
            • Re: Performance problems on MySQLAlexandre Vieira3 Sep
              • Re: Performance problems on MySQLJohnny Withers3 Sep
                • Re: Performance problems on MySQLAlexandre Vieira3 Sep
                  • Re: Performance problems on MySQLJohnny Withers3 Sep
                    • Re: Performance problems on MySQLMySQL)5 Sep
  • Re: Performance problems on MySQLmos6 Sep
    • Re: Performance problems on MySQLAlexandre Vieira6 Sep
      • Re: Performance problems on MySQLAlexandre Vieira6 Sep