Improve SELECT statement for device's latest position

[Jeferex]

I turned on sql slow query and I got a lot of slow querys that run for more than 10 sec.

The query is this, I suppose is to get the latest position of user devices.

select position0_.id as id2_, position0_.address as address2_, position0_.altitude as altitude2_, position0_.course as course2_, position0_.device_id as device12_2_, position0_.latitude as latitude2_, position0_.longitude as longitude2_, position0_.other as other2_, position0_.power as power2_, position0_.speed as speed2_, position0_.time as time2_, position0_.valid as valid2_ from positions position0_ where position0_.id in (select device1_.latestPosition_id from devices device1_, positions position2_ where device1_.latestPosition_id=position2_.id and (device1_.id in (323 , 322 , 324 , 325 , 294 , 276 , 103 , 105 , 102 , 101 , 88 , 86 , 85 , 84 , 49 , 37 , 35 , 33 , 22 , 21 , 20 , 15 , 5 , 4 , 3 , 2 , 1 , 14 , 17 , 18 , 95 , 19 , 23 , 24 , 36 , 87 , 195 , 261 , 277 , 327)));

This user account has 60 devices so far.

Any ideas for improving this query because I think this is the reason my mysql server crashes.

I've been reading that using IN in a clause is very inefficient. Check http://stackoverflow.com/questions/15881295/high-cpu-consuming-mysql-request-with-php

Thanks in advance

[tananaev]

In the new interface all database queries are provided in the config and should be more optimal.