php - Indexing Mysql Group BY Query

Question

Hi I Have a table called train_stop which have ~1 million rows.

I have following query-

SELECT * FROM (SELECT * FROM train_stop where code='XYZ' AND active=1 UNION SELECT * FROM train_stop where code='ABC' UNION SELECT * FROM train_stop where code='STU'...... ) AS t3 GROUP BY t3.number order by departs

This query uses only same table train_stop. First i select certain rows based upon code and then group it by number. I have tried indexing different columns but above query always uses using temporary, using filesort. Execution time is of the order of seconds. Please tell if there could be better way to write above query and indexing strategy so as to optimize and get results in milliseconds. Your help would be much useful.

Create Statement is `CREATE TABLE `train_stop` (
  `number` varchar(1000) NOT NULL,
  `stop_number` int(11) NOT NULL,
  `code` varchar(1000) NOT NULL,
  `station name` varchar(1000) NOT NULL,
  `arrives` time NOT NULL,
  `departs` time NOT NULL,
  `halt` varchar(1000) NOT NULL,
  `pf` varchar(1000) NOT NULL,
  `day` int(11) NOT NULL,
  `km` varchar(1000) NOT NULL,
  `speed` varchar(1000) NOT NULL,
  `elev` varchar(1000) NOT NULL,
  `zone` varchar(1000) NOT NULL,
  `address` varchar(1000) NOT NULL,
  `active` int(11) DEFAULT '1',
  KEY `index_1` (`number`(767),`code`(767)),
  KEY `PIndex` (`number`(767),`stop_number`),
  KEY `three_columns_idx` (`code`(767),`active`,`departs`),
  KEY `two_columns_idx` (`code`(767),`active`),
  KEY `two_columns_group_idx` (`number`(767),`departs`),
  KEY `one_columns_group_idx` (`departs`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1`

Answer 1

I think your query traverse whole table one time for each subquery and this is your bottleneck. please try this one

SELECT * FROM train_stop where (code='XYZ' AND active=1) OR (code='ABC') OR (code='STU') ...
Group by number order by departs

and with regard to your query which uses GROUP BY statement, I think you cannot write select * please narrow the selection phrase!!!