使用cakephp从mysql数据库中获取大量数据

Question

I have 7 similar tables named 'device_1_updates', 'device_2_updates', 'device_3_updates'...'device_7_updates'.

I am using UNION to face data from these 7 tables, But it is taking too much time .. since data in each of these 7 tables are too heavy.

Please suggest some way to make it fast

my sql query is:

SELECT 
  * 
FROM
  (SELECT 
    * 
  FROM
    device_1_updates 
  UNION
  SELECT 
    * 
  FROM
    device_1_updates 
  UNION
  SELECT 
    * 
  FROM
    device_2_updates 
  UNION
  SELECT 
    * 
  FROM
    device_3_updates 
  UNION
  SELECT 
    * 
  FROM
    device_4_updates 
  UNION
  SELECT 
    * 
  FROM
    device_5_updates 
  UNION
  SELECT 
    * 
  FROM
    device_6_updates 
  UNION
  SELECT 
    * 
  FROM
    device_7_updates) AS device_data 
WHERE device_time > '2014-04-05 01:55:08' 
  AND device_time < '2014-04-11 11:55:08' 
  AND imei = 357804045965906 
ORDER BY device_time ASC 
LIMIT 0, 100

and the corresponding response is:

"response": [{
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:57:52",
            "lat": "",
            "lang": "",
            "event": "^6"
        }
    }, {
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:58:02",
            "lat": "",
            "lang": "",
            "event": "^4"
        }
    }, {
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:58:14",
            "lat": "",
            "lang": "",
            "event": "^8"
        }
    }, {
        "device_data": {
            "imei": "357804045965906",
            "device_time": "2014-04-02 15:58:19",
            "lat": "",
            "lang": "",
            "event": "^1"
        }
    }
]

Answer 1

The easiest way to speed up that query would be to use UNION ALL instead of UNION. UNION will only return distinct values, meaning it has to check if they are distinct. UNION ALL returns everything, including duplicates. So if the tables don't contain duplicate data, use UNION ALL.

To go further you would want to make sure you have proper indexes in place.