1

I have a MySQL database with over 10k rows. When I execute the query to get all rows within 24 hours, it takes very long to get the results: 

$sql = 'SELECT *, DATE_FORMAT(m.time, "%d.%m.%Y %H:%i:%s") AS date 
  FROM markers m,pid p 
  WHERE m.time >=DATE_SUB(NOW(), INTERVAL 24 HOUR) 
  AND m.pidID=p.id';

I have read about indexing of tables, but could not adapt it to my query. Can anyone please explain how to change the above query with indexing so the query does not a full table scan?

SIDU
  • 2,258
  • 1
  • 12
  • 23
Jimmy
  • 13
  • 6
  • Indexes are not shown in queries. They are in the table's configuration. I would try to add an index on `m.time` , `m.pid` and `p.pid` – litelite Aug 03 '16 at 20:03
  • See ["How do I add indexes to mysql tables?"](http://stackoverflow.com/questions/3002605/how-do-i-add-indexes-to-mysql-tables) – Don't Panic Aug 03 '16 at 20:10
  • The index is not part of the query. You have to alter your table to add the index *before* trying to execute the query. – Don't Panic Aug 03 '16 at 20:12
  • ok I added index to fields m.time, p.pidID and p.id. I will test it and see if this affects the performance... – Jimmy Aug 03 '16 at 20:32

1 Answers1

0

Queries generally benefit from indexes on fields the used in their WHERE and JOIN...ON conditions (and ORDER|GROUP BY lists).

Uueerdo
  • 15,723
  • 1
  • 16
  • 21