Fetching heavy data from mysql database using cakephp

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"
        }
    }
]

Not sure how MySQL optimize the query. But try to add the WHERE statement to each table before you union them. What indexes exists on the tables? And why are you having 7 tables sounds really strange. And that is one reason it is slow — WizKid
– WizKid, Commented May 21, 2014 at 6:17

M Khalid Junaid · Accepted Answer · 2014-05-21 06:27:30Z

I would suggest you to have one table device_updates with an additional column as type where you can have 1-7 numbers ,but for now you need to add index for all of your 7 tables on device_time ,imei columns then use UNION ALL so it will not remove duplicates ,using union MySQL will sort the combined dateset into a temp table once data set is sorted it will remove duplicates,to optimize it push ORDER BY, LIMIT and WHERE conditions inside each subquery

(SELECT * FROM  device_1_updates 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 )
UNION ALL
(query 2 with where limit and order by)
UNION ALL
(query 3 with where limit and order by)
.
.
.
.
ORDER BY device_time ASC LIMIT 0, 100 /*at the end of query add order by and limit*/

Make sure you add indexes for the columns in where clause,lat thing i would say just select only need columns using select * will also reduce speed if there are too many columns

For reference

user3071296 · Accepted Answer · 2014-05-21 06:13:35Z

0

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.

answered May 21, 2014 at 6:13

user3071296

1032 silver badges8 bronze badges

Comments

mHouses · Accepted Answer · 2014-05-21 06:20:20Z

0

As far as I know, an UNION is a very slow operation in SQL. And for your code, it seems that device_2_updates, device_3_updates and so on are equal, isn't it?

In my opinion, what you can do is to join all the tables in one: DEVICE_UPDATES: device_id, imei, device_time, lat, lang, event

And define, at least three indexes: device_id, imei, device_time

Another thing you can do (I'm not really sure if this will increase performance) is change your WHERE clause, and instead of using device_time < 'time1' and device_time > 'time2', use a device_time between 'time1' and 'time2'.

answered May 21, 2014 at 6:20

mHouses

9191 gold badge17 silver badges37 bronze badges

3 Comments

Rahul Kumar Over a year ago

I used 7 tables because there are huge amount of data in each table (approx 300 MB of data in each table) and it is growing on every second.. don't know how much data does mysql alow in 1 table.. any guess??

Rahul Kumar Over a year ago

Thanks WizKid, M Khalid Junaid, mHouses and user3071296.. :)

mHouses Over a year ago

@user3517995 As MySQL documentation say (dev.mysql.com/doc/refman/5.0/en/table-size-limit.html), if you're running Linux you can have up to 4TB of data in a MySQL table. So don't worry, and join all the tables in one.

Collectives™ on Stack Overflow

Fetching heavy data from mysql database using cakephp

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related