Query to get last event in EventData table

[NELSON RODRIGUEZ]

Hello I use the following query to get last eventdata row in eventdata table:
select * from gts.EventData where deviceID='don005' order by timestamp desc limit 1;

but it slower and efficient query, i see that opengts gets faster this row, the answer is ¿ what is the query that use Opengts to retrieve last eventdata row for a deviceID vehicle???

Thanks

Best Regards

Nelson Rodriguez

[joseluisgudino]

Hi, for a good performance of opengts you should check: 1) the server, 2) the processor characteristics and 3) the amount of ram memory, also you must optimize the mysql variable to obtain the best possible performance.

I have worked with the gts, hosting more than 3000 devices without problems

sorry for my bad English

De: NELSON RODRIGUEZ hertzon@users.sourceforge.net
Enviado el: miércoles, 9 de mayo de 2018 12:38 p. m.
Para: [opengts:discussion] 579835@discussion.opengts.p.re.sourceforge.net
Asunto: [opengts:discussion] Query to get last event in EventData table

Hello I use the following query to get last eventdata row in eventdata table:
select * from gts.EventData where deviceID='don005' order by timestamp desc limit 1;

but it slower and efficient query, i see that opengts gets faster this row, the answer is ¿ what is the query that use Opengts to retrieve last eventdata row for a deviceID vehicle???

Thanks

Best Regards

Nelson Rodriguez

---

Query to get last event in EventData tablehttps://sourceforge.net/p/opengts/discussion/579835/thread/1bdcdbb7/?limit=25#16e6

---

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/opengts/discussion/579835/

To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

[tea4two]

Dear Nelson,

It seems to me that you use an external software to query the DB.

To achieve maximum performance you need to use OPENGTS Framework, or an SQL WHERE with indexed fields.
Your query use only deviceID so the query make a FULL TABLE SCAN to retrieve results.
I don't remember in details which indexes are used in the EventData Table, but you can find It using mysql client CLI or PHPMYADMIN.

Using correct WHERE conditions you'll have a great speed improvement.
Let me know....

• Regards
• Pierluigi

[NELSON RODRIGUEZ]

Hello Pierluigi thanks for your help, please can you give some idea of how make a fast and efficient sql query to get the last event record on eventdata record of given deviceID vehicle, the columns of this table are:

Best regards

Nelson

[tea4two]

Dear Nelson,

try the follow query ;-).

SELECT * FROM gts.EventData WHERE deviceID='wnz400' AND accountID='beniamin' ORDER BY timestamp DESC LIMIT 1;

and compare execution time with the follow:

SELECT * FROM gts.EventData WHERE deviceID='wnz400' ORDER BY timestamp DESC LIMIT 1;

Let me know the differece.

Regards,
- Pierluigi

[Sandeep]

Hello Nelson,

I forget table names now. But I think there is one table Device where all
device details is stored. In that table lastEvent Data is stored. You can
query that table directly. Its automatically update lastEvent data every
time EventData table insert new entry.

And if any data is not there which you want you can create trigger, which
will update data into Device table

This is the fasted method. Hope this will work for you.
Share your email id if you can't figure it out.

On Thu, May 10, 2018 at 8:32 PM, tea4two tea4two@users.sourceforge.net
wrote:

Dear Nelson,

try the follow query ;-).

SELECT * FROM gts.EventData WHERE deviceID='wnz400' AND
accountID='beniamin' ORDER BY timestamp DESC LIMIT 1;

and compare execution time with the follow:

SELECT * FROM gts.EventData WHERE deviceID='wnz400' ORDER BY timestamp
DESC LIMIT 1;

Let me know the differece.

Regards,
- Pierluigi

---

Query to get last event in EventData table
https://sourceforge.net/p/opengts/discussion/579835/thread/1bdcdbb7/?limit=25#d852

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/opengts/discussion/579835/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

--
╰» Regards
╰» ѕαη∂єєρ

[NELSON RODRIGUEZ]

Ufff.... thanks Pierluigi, the velocity of execution of the query increase from 3 seconds to 0.1 seconds, you saved my day, excuse for my low basic SQL languaje expertise.

Best Regars