有没有办法在PHP中优化这个mysql代码？

Question

SELECT tblstud.studid,
       tblsched.ip
FROM tblstud
LEFT JOIN tblstudsched ON (tblstud.studid=tblstudsched.studid)
LEFT JOIN tblsched ON (tblstudsched.schedid=tblsched.schedid)
WHERE tblstud.rfidid='".$rfid."'
  AND tblsched.ip='".$ip."'
  AND EXISTS
    (SELECT tblsched.schedid
     FROM tblsched
     INNER JOIN tblstudsched
     WHERE tblstudsched.schedid=tblsched.schedid
       AND NOW() BETWEEN tblsched.timein AND tblsched.timeout)

This is my code so far and it is designed to be done after Arduino microcontroller sends data to PHP. IT takes me about 8 seconds before another data is to be accepted. I haven't checked the Arduino side but I think it's fast enough when I did another different mysql query but that one is relatively shorter than the one above.

The tables are in Inno DB but I'll try MyISAM later since the latter is the best when there are more readings than adding data.

The data types are Varchar with ranges varying from 3-30 characters each.

I'm confident also with the speed of the laptop I'm using.

Answer 1

This is your query:

SELECT tblstud.studid, tblsched.ip
FROM tblstud LEFT JOIN
     tblstudsched
     ON (tblstud.studid = tblstudsched.studid) LEFT JOIN
     tblsched
     ON (tblstudsched.schedid = tblsched.schedid)
WHERE tblstud.rfidid = '".$rfid."' AND tblsched.ip = '".$ip."' AND
      EXISTS (SELECT tblsched.schedid
              FROM tblsched INNER JOIN
                   tblstudsched
                   ON tblstudsched.schedid = tblsched.schedid AND
                      NOW() BETWEEN tblsched.timein AND tblsched.timeout
              );

First, the LEFT JOIN in the outer query is unnecessary, because the WHERE conditions just turn it into an INNER JOIN anyway. Second, your EXISTS looks suspicious. It is just looking to see if there are any active schedules at all in the data. So, it is going to either filter everything out or nothing out. It is possible that you intend:

SELECT tblstud.studid, tblsched.ip
FROM tblstud INNER JOIN
     tblstudsched
     ON (tblstud.studid = tblstudsched.studid) INNER JOIN
     tblsched
     ON (tblstudsched.schedid = tblsched.schedid)
WHERE tblstud.rfidid = '".$rfid."' AND tblsched.ip = '".$ip."' AND
      EXISTS (SELECT 1
              FROM tblsched
              WHERE tblstudsched.schedid = tblsched.schedid AND
                    NOW() BETWEEN tblsched.timein AND tblsched.timeout
              );

However, I suspect that you want schedules that meet the where conditions. If so, this may be the logic that you want:

SELECT tblstud.studid, tblsched.ip
FROM tblstud LEFT JOIN
     tblstudsched
     ON (tblstud.studid = tblstudsched.studid) LEFT JOIN
     tblsched
     ON (tblstudsched.schedid = tblsched.schedid)
WHERE tblstud.rfidid = '".$rfid."' AND tblsched.ip = '".$ip."' AND
      NOW() BETWEEN tblsched.timein AND tblsched.timeout;

If this suspicious is true, then you want indexes on tblstud(rfidid, studid), tblsched(schedid, ip, timein, timeout) and, of course, tblstudsched(studid, schedid). Note that these are composite indexes with multiple keys.