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 <mo...@fastmail.fm> 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 <joh...@pixelated.net>
>> 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 <nul...@gmail.com>
>> 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 - nul...@gmail.com
>> >>
>> >>
>> >
>> >
>> > --
>> > -----------------------------
>> > Johnny Withers
>> > 601.209.4985
>> > joh...@pixelated.net
>> >
>>
>>
>>
>> --
>> Alexandre Vieira - nul...@gmail.com
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=nul...@gmail.com
>
>
--
Alexandre Vieira - nul...@gmail.com
