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

We're chaning it to INT(9). Apparently someone remembered to change the type
of data in this field from an alphanumeric value to an INT(9).

I'm going to change this asap.

Thanks

BR
AJ

On Mon, Sep 6, 2010 at 5:17 AM, mos <mos99@stripped> wrote:

> At 04:44 AM 9/3/2010, Alexandre Vieira wrote:
>
>> 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
>>
>
> Alexandre,
>     Do you have UserId declared as CHAR? It looks numeric to me. If it is
> stored as an integer then don't use the '     ' in the select statement
> otherwise it needs to convert it.
> If UserId values are integers and you have the column defined as CHAR, then
> declare the column UserId as integer or BigInt and the searches should be
> faster than searching on CHAR.
>
> Mike
>
>
>
>
>  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
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
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