1

Please see the table structure below:

-- Table structure for table `station_listing`
CREATE TABLE IF NOT EXISTS `station_listing`(
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `lid` bigint(11) NOT NULL,
  `cid` bigint(11) NOT NULL,
  `name` varchar(300) NOT NULL,
  `type` enum('homeservices','restaurants') NOT NULL,
  `location` varchar(300) NOT NULL,
  `stationID` varchar(300) NOT NULL,
  `claimed` tinyint(1) NOT NULL,
  `closed` tinyint(1) NOT NULL,
  `reviewcount` int(6) NOT NULL,
  `rating` decimal(10,1) NOT NULL,
  `city` varchar(300) NOT NULL,
  `categories` varchar(300) NOT NULL,
  `dealcount` int(6) NOT NULL,
  `result_month` varchar(10) NOT NULL,
  `updated_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `stationID` (`stationID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

-- Dumping data for table `station_listing`
INSERT INTO `station_listing`(
   `id`, `lid`, `cid`, `name`, `type`, `location`,
   `stationID`, `claimed`, `closed`, `reviewcount`, `rating`, `city`,
  `categories`, `dealcount`, `result_month`, `updated_date`
) VALUES (
   310837, 115, 151, 'Apartment Building', 'homeservices', 'West Hollywood',
  'apartment-building-los-angeles', 0, 0, 1, '1.0', 'Los Angeles',
  'apartments', 0, 'Jan', '2014-01-15'
), (
  314, 4, 1, 'King of Shawarma', 'restaurants', 'Calgary',
  'king-of-shawarma-calgary-2', 0, 0, 0, '0.0', 'Calgary', 
  'afghani,halal,falafel', 0, 'Jan', '2014-02-14');

I have a table which consist of details of different business from stations, which are updated every month.

stationID will be updated once in every month with its parameters.

I need to get stationIDs which are common for a selected range of months.For example If I select a range from Jan to Mar,this should fetch all the stations which are common for moths JAn,Feb And MAr.

We tried GROUP BY to group all the stations which appears for the months.

I am facing the following issues,

When I try a query like: 

  SELECT stationID 
    FROM station_lisitng 
GROUP BY `stationID``

I am getting the whole list of stationIDs

But when trying to fetch multiple columns, like: 

SELECT id, stationID FROM station_lisitng GROUP BY `stationID`;

We are getting and phpMyAdmin Error as 'Showing 0 to -1 results' and we are neither getting the results nor its count.

Our table consist of a large number of entries( 1 million), and we doubt grouping this is making the trouble. Along with there are station IDs with special characters too!

stationID column is indexed and its the only identifiable entity for each station.

SELECT id, stationID FROM station_lisitng GROUP BY `stationID` LIMIT 0,900000`

The above works but consuming lot of time.

It would be great, if you could help us to group this table with stationIDs.

We tried the following query, and we are not satified with its performance:

SELECT `stationID`,
       name,
       lid,
       cid,
       type,
       location, 
       COUNT(*) c 
FROM `yc_biz_listing` 
WHERE DATE_FORMAT(`updated_date`, '%m %Y') BETWEEN '01 2014' AND '03 2014' 
GROUP BY `yelpID` HAVING c = 3

Any help will be highly appreciated. Thanks in advance.

Stan
  • 8,710
  • 2
  • 29
  • 31
suneesh
  • 192
  • 1
  • 2
  • 10
  • Can you add a SQLFiddle? – Neels Feb 20 '14 at 12:09
  • Sorry, the issue is not happening for small number of data. Problem comes when we have 1 million entries. – suneesh Feb 20 '14 at 12:11
  • Atleast we cud check the Explain statement. Anyways, you can try doing Explain on your query and then see if the index is getting utilized or not. – Neels Feb 20 '14 at 12:13
  • [`GROUP BY`](https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html), [`DISTINCT` vs `GROUP BY`](http://stackoverflow.com/questions/581521/whats-faster-select-distinct-or-group-by-in-mysql) – Alexander Feb 20 '14 at 12:17
  • 1
    I would suggest starting by using the ANSI `GROUP BY, meaning GROUP BY all columns that are not aggregate fields. Also, when you LIMIT, use an ORDER BY. In your last example (with the DATE_FORMAT) you should re-engineer your WHERE clause such that there is NOT a function on the updated_date column, which may help with index usage. – AgRizzo Feb 20 '14 at 12:20
  • @AgRizzo Can u please suggest the ANSI `GROUP BY` query for the above select query. – suneesh Feb 20 '14 at 12:50
  • Just GROUP BY all columns that are not an aggregate function. In **Case 2**, `GROUP BY id, stationID`. In the final query: `GROUP BY yelpID,name,lid,cid,type,location` – AgRizzo Feb 20 '14 at 12:54

0 Answers0