MySQL Forums
Forum List  »  Newbie

Creating Index after Partitioning the MySQL Table?
Posted by: Deepu Pawar
Date: August 04, 2012 01:04AM

I have mysql table Stop_Times with 20 Million records.

I am using MyISAM storage engine for this table.

My table structure is

| agency_id | varchar(50) | NO | | NULL | |
| trip_id | varchar(50) | NO | PRI | NULL | |
| arrival_time | time | NO | | NULL | |
| departure_time | time | NO | | NULL | |
| stop_id | varchar(50) | NO | PRI | NULL | |
| stop_sequence | int(11) | NO | PRI | NULL | |
| route_id | varchar(50) | NO | | NULL | |
| route_type | int(5) | NO | | NULL | |
+---------------------+-------------+------+-----+--------

The output of **show create table Stop_Times** is -

CREATE TABLE `Stop_Times` (
`agency_id` varchar(50) NOT NULL,
`trip_id` varchar(50) NOT NULL,
`arrival_time` time NOT NULL,
`departure_time` time NOT NULL,
`stop_id` varchar(50) NOT NULL,
`stop_sequence` int(11) NOT NULL,
`route_id` varchar(50) NOT NULL,
`route_type` int(5) NOT NULL,
UNIQUE KEY `idx_Stop_Times` (`agency_id`,`trip_id`,`stop_sequence`,`stop_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (stop_id)
PARTITIONS 250 */

I have one query

select distinct trip_id, stop_sequence from Stop_Times where agency_id = ? and stop_id = ?

Before partitioning the table this query took more than 4-5 mins.

But now I have partitioned the table on **stop_id** like
**partition by key(stop_id)**

Now this query is taking 2-3 seconds to execute.

I have another query followed by the above query -

select distinct(stop_id) from Stop_Times where agency_id = ? and trip_id = ? and stop_sequence > ?

The output of **explain** on above query is -

1 SIMPLE Stop_Times range idx_Stop_Times idx_Stop_Times 308 NULL 250 Using where; Using index; Using temporary

This query is taking more time 90 - 150 seconds.

So my question is **do I need to create Index on trip_id and stop_sequence ?**
Will that speed up the query performance?

Do I need to change the storage engine **from MyISAM from InnoDB** since there are many reads(select queries) for this table from multiple users at a time?

Please Database gurus help me.

Thank You

Deepu

Options: ReplyQuote


Subject
Written By
Posted
Creating Index after Partitioning the MySQL Table?
August 04, 2012 01:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.