PDO查询运行缓慢

Question

I've looked and tried many different solutions, but none seemed to work. I'm running the following query to fetch results from a 100k table:

SELECT * FROM (SELECT expenses.id AS eID, expenses.date, 
                        expenses.receipt, expenses.purpose, expenses.date_submitted, 
                        expenses.expense_type, expenses.description, 
                        expenses.curr_used,expenses.reimbursement_amount, 
                        expenses.hst_amount, expenses.amex_amount, expenses.gl_acct, 
                        expenses.net_amount,expenses.status, users.id AS uID,  
                        users.firstname, users.lastname, users.department, 
                        users.adminship_level, users.is_finance 
                FROM expenses 
                    INNER JOIN users on expenses.user_id = users.id
            )  as expense_info 
WHERE (
        (department="Publishing" 
        AND adminship_level < 4) 
    OR uID=8
    ) 
ORDER BY date_submitted DESC 
LIMIT 15 

The expenses table is the one with the 100k rows, whilst the users table has 500 users.

The users table is tied to the expenses w/ a foreign key on user_id, and also contains a composite index on department+adminship level.

EXPLAINing yields the following

Running this w/ MySQL Workbench works fine and is very fast. However, placing the exact same query into php and using PDO's prepare() + execute() functions takes 6 whole seconds to fetch the 15 results.

Any ideas on how I can improve the speed? What am I doing wrong?

I'm using XAMPP, but without its MariaDB (instead I use MySQL 8)

Please ask for extra details that might help and i'll provide them

Answer 1

Erm,

SELECT e.id eID
     , e.date
     , e.receipt
     , e.purpose
     , e.date_submitted
     , e.expense_type
     , e.description
     , e.curr_used
     , e.reimbursement_amount
     , e.hst_amount
     , e.amex_amount
     , e.gl_acct
     , e.net_amount
     , e.status
     , u.id uID
     , u.firstname
     , u.lastname
     , u.department
     , u.adminship_level
     , u.is_finance 
  FROM expenses e
  JOIN users u
    ON e.user_id = u.id
 WHERE (u.department = "Publishing" AND u.adminship_level < 4) 
    OR uID=8
 ORDER 
    BY e.date_submitted DESC 
 LIMIT 15