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

MySQL query is extremely long from PHP – Phpmyadmin

I have a query that takes 0.0002s in PHPMyAdmin and takes hundreds of seconds if I do it from PHP. Here it is:

    SELECT  id, id_pages, link, childlink, url, hiddencontent, cansearch,
        (
            SELECT  p.id
                FROM  pages as p
                WHERE  pages.hiddencontent=1
                  AND  p.id_pages IS NOT NULL
                  AND  p.hiddencontent=0
                  AND  pages.id=p.id_pages
                order by  p.npp asc
                limit  1
        ) as id_firstchild
        FROM  pages

It returns around 24k rows and I don’t know why it takes so long. My friend tried it on his PC and it worked lightning fast and his pc is not better. I don’t know the reason of this PHP behavior, maybe I should make some changes in the configuration file?

Tags:

2

Answers


  1. 0

    Whenever you try to decide if searching or fetching is slow, use a LIMIT 1 at the end of your query (and comment out the ORDER BY part if there’s any). This way, you get the first row so you will know how long that takes. It should be blazing fast.

    Another useful information is when you enclose the whole query in another, like SELECT ...FROM (SELECT ...), in which case the outer query should count the rows returned. This will give you the total time needed to identify all rows that would be fetched, but without actually fetching them. This is useful to determine if you wrote your SQL query poorly, or it’s just a lot of data to fetch. (If both of the above go fast and you still get a slow query, it’s the fetch.)

    You can also make use of EXPLAIN to see if your performance issue is because of insufficient or improper indexing.

    As for phpmyadmin, the first comment on your post pretty much sums it up: phpmyadmin uses a LIMIT so it will run faster even if your query itself is slow.

    Login or Signup to reply.
  2. 0

    You have two questions:

    Why the timing?

    Is the "Query cache" turned on? That’s about the only way it can run in 0.2ms. (Any non-trivial SELECT that runs in under 1ms almost certainly did not run, but was found in that cache.)

    And, as pointed out by others, phpmyadmin silently adds a LIMIT. However, other clues (mostly in Comments) point to the Query cache giving anomalous results.

    How to speed up.

    SELECT  id, id_pages, link, childlink, url, hiddencontent, cansearch,
        if (hiddencontent = 1, NULL,   -- to avoid doing the SELECT
            ( SELECT  p.id
                FROM  pages as p
                WHERE  p.hiddencontent = 0
                  AND  outer.id = p.id_pages -- fails on NULL
                order by  p.npp asc
                limit  1
           )) as id_firstchild
    FROM  pages AS outer    -- clarify which is which

    and have this ‘composite’ and ‘covering’ index:

    INDEX(hiddencontent, id_pages, npp, id)

    Two improvements:

    • Avoid calling the subquery when not needed.
    • Have an index that will allow the subquery to look at only one row, and only in the index’s BTree, hence be ‘blazingly fast’.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search