Re: [PHP-DB] Table optimization ideas needed

Thank you very much, Chris. :)
Fyi,


On Wed, Mar 26, 2008 at 1:27 PM, Chris <[email protected]> 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