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
