List:General Discussion« Previous MessageNext Message »
From:mos Date:September 6 2010 4:17am
Subject:Re: Performance problems on MySQL
View as plain text  
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

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