Thank you very much, Chris. :)
Fyi,
On Wed, Mar 26, 2008 at 1:27 PM, Chris <dmagick@xxxxxxxxx> wrote:
> Shelley wrote:
>
> >
> > +--------------+-----------------------+------+-----+-------------------+----------------+
> > | Field | Type | Null | Key | Default
> > | Extra |
> >
> > +--------------+-----------------------+------+-----+-------------------+----------------+
> > | id | int(11) | | PRI | NULL
> > | auto_increment |
> > | owner_id | int(11) | | MUL | 0
> > | |
> > | owner_name | varchar(50) | | |
> > | |
> > | visitor_id | int(11) | | MUL | 0
> > | |
> > | visitor_name | varchar(100) | | |
> > | |
> > | visit_time | timestamp | YES | | CURRENT_TIMESTAMP
> > | |
> > | first_time | int(10) unsigned | | | 0
> > | |
> > | last_time | int(10) unsigned | | MUL | 0
> > | |
> > | visit_num | mediumint(8) unsigned | | | 0
> > | |
> > | status | tinyint(3) unsigned | | MUL | 0
> > | |
> >
> > +--------------+-----------------------+------+-----+-------------------+----------------+
> >
> > That's the table which has more than 20 million records.
> >
>
> And what query are you running?
>
> What does:
>
> explain your_query_here;
>
> show?
mysql> explain select count(*) from message;
+----+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
| 1 | SIMPLE | message | index | NULL | status | 1 |
NULL | 23051499 | Using index |
+----+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
Three queries return the same results.
>
>
> I can see indexes on at least owner_id, visitor_id, last_time and status,
> but at least one of those is across multiple columns ('MUL').
>
> Can you show us the index definitions:
>
> show indexes from table_name;
>
> or
>
> show create table table_name;
>
> and just include the indexes at the bottom.
mysql> show indexes from message;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| message | 0 | PRIMARY | 1 | id | A
| 23051499 | NULL | NULL | | BTREE | |
| message | 1 | owner_id | 1 | owner_id | A
| 922059 | NULL | NULL | | BTREE | |
| message | 1 | visitor_id | 1 | visitor_id | A
| 501119 | NULL | NULL | | BTREE | |
| message | 1 | status | 1 | status | A
| 18 | NULL | NULL | | BTREE | |
| message | 1 | last_time | 1 | last_time | A
| 11525749 | NULL | NULL | | BTREE | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (1.09 sec)
>
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
--
Regards,
Shelley
