phpmyadmin Question posted in
The official documentation can be found here.

Mysql slow-query only in script, but not in phpmyadmin. How to solve that?

I have a simple select statement without any join.
All it does is checking a date_time and some other flags

table has about 45.000 entries.
The query is performed within a php script every 5 minutes, to send out an email (it sends out 1 email if it finds an entry or none)

I have been adding an index for exactly this query, and when I use EXPLAIN the index is used. So I was able to make this query really fast (0.0006 seconds). But it does not help.

select  *
    from  tablex
    where  bz <> 'vok'
      and  date_checkout < DATE_ADD(sysdate(),INTERVAL -15 MINUTE)
      and  date_checkout > '0000-00-00 00:00:00'
      and  bx=0
      and  bz <> ''
      and  nsem=0
      and  email <> '[email protected]'

This query fills my slow-query log (I set it to 0.4 seconds)
Within the php script the query takes around 0.7 seconds.

When I copy the query and use it on phpmyadmin it takes 0.0006 seconds.

Why is there a difference for this query within the php script and from the phpmyadmin console?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So what I learned for now is, that the query-time shown in phpmyadmin does not reflect the real query time within a php script. (also when I used SQL_NO_CACHE)

    I have no explanation for the time difference (0.7 vs 0.0006 seconds).

    I did not change my SQL statement, but I updated the table flag nsem to -1 for all entries older than 2 days, so for now the SQL is just using nsem as an index for the query (and not the big index). nsem was a flag set to 1 when an email has been send, or 0 if not (not all entries need an email send...and they got a -1 now). Query is fast now.


  2. 0

    This composite index may help:

    INDEX(bx, nsem,        -- first, in either order
      date_checkout)   -- last

    For further discussion, please provide SHOW CREATE TABLE.

    If you don’t need all the columns, spell out just the ones you do need.

    Is there a reason for using SYSDATE() instead of NOW()?

    0.0006 smells like the Query cache was used.

    it sends out 1 email if it finds an entry or none

    Then tack on LIMIT 1?

    Try to avoid needing so many flags (zero date_checkout, nsem=0, bx=0, etc)

    0.7s every 300 seconds is really insignificant.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search