Re: [PHP-DB] Table optimization ideas needed

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?

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.

-- 
Postgresql & php tutorials
http://www.designmagick.com/