mySql optimize a query

Question

I'm looking to get todays last 10 locations from my database for each driver (authentication_id). But have had to write two separate queries, the latter using the resultset of the former. How could I write it as a single query?

SELECT DISTINCT authentication_id FROM gps_trak WHERE DATE(location_timestamp) = DATE(NOW())

loop the resultset for each $authID...

SELECT latitude, longitude FROM gps_trak WHERE authentication_id = ".$authID." ORDER BY location_timestamp DESC LIMIT 10";

Is it possible?

Joel Harkes · Accepted Answer · 2014-10-28 14:23:26Z

2

You can put a query inside a query. This is called a 'subquery'

example

SELECT latitude, longitude 
FROM gps_trak 
WHERE authentication_id  
IN (
    SELECT DISTINCT authentication_id 
    FROM gps_trak 
    WHERE DATE(location_timestamp) = DATE(NOW())
) 
ORDER BY location_timestamp DESC LIMIT 10";

Performance optimization

Also your question also hinted about performance (optimization). If this is a problem you could try puting indexes on the columns you try to filter (columns used in WHERE statements). There are alot of ways you can optimize performance. This one (indexes), and rewriting your queries are the best way to increase performance. More on performance: http://www.infoworld.com/article/2616674/database-administration/10-essential-performance-tips-for-mysql.html

edited Oct 28, 2014 at 14:23

answered Oct 28, 2014 at 13:57

Joel Harkes

11.7k3 gold badges49 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fejese · Accepted Answer · 2014-10-28 14:40:06Z

0

try like this:

SELECT latitude, longitude FROM gps_trak WHERE authentication_id IN (
  SELECT DISTINCT authentication_id FROM gps_trak
  WHERE DATE(location_timestamp) = DATE(NOW())
) ORDER BY location_timestamp DESC LIMIT 10";

edited Oct 28, 2014 at 14:40

fejese

4,6284 gold badges32 silver badges37 bronze badges

answered Oct 28, 2014 at 13:56

Naveed

195 bronze badges

Comments

Strawberry · Accepted Answer · 2014-10-28 14:48:54Z

0

Here's a correlated subquery version (untested)...

SELECT DISTINCT x.authentication_id 
           FROM gps_trak x
           JOIN 
              ( SELECT latitude
              , longitude 
           FROM gps_trak 
          WHERE authentication_id = x.authentication_id
          ORDER 
             BY location_timestamp DESC LIMIT 10
              ) y
          WHERE x.location_timestamp BETWEEN CONCAT(CURDATE(),' 00:00:00') AND CONCAT(CURDATE(),' 23:59:59');

answered Oct 28, 2014 at 14:48

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

mySql optimize a query

3 Answers 3

example

Performance optimization

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

example

Performance optimization

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related