3

I'm having a problem optimizing a MySQL Query, but so far it takes too long to pull a simple result limited to 500 records.

My query is this:

SELECT ou.*, gr.accepted, gr.value 
FROM offer_usermeta ou 
LEFT JOIN gateway_requests AS gr ON ou.customer_id = gr.customer_id 
WHERE ou.customer_id != "" 
AND ou.created >= '2012-10-08 00:00:00' 
AND ou.created <= '2012-10-08 23:59:59' 
ORDER BY ou.created DESC LIMIT 500

This query is taking an entire minute to iterate over maybe 40,000 records. I need it to pull in gateway responses based on the customer ID if there are any and the data looks correct with this query, but I need tips on optimizing. I was considering just pulling the data in separately and foreach'ing it together as necessary, but I know that's probably going to be far slower...

Any tips? (I'm using codeigniter if anyone knows a fancy trick in there as well)

Improve this question
1
  • 1
    Please post your tables' structure. Commented Oct 15, 2012 at 19:12

2 Answers 2

Reset to default
2

Add an index on the created column if you haven't so far. The DB will have to fetch all records to do the order by and match the where condition no matter if you have a limit of only 500 records. After that try

SELECT ou.*, gr.accepted, gr.value 
FROM offer_usermeta ou 
LEFT JOIN gateway_requests AS gr ON ou.customer_id = gr.customer_id 
WHERE ou.created between '2012-10-08 00:00:00' and '2012-10-08 23:59:59' 
ORDER BY ou.created DESC 
LIMIT 500
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

"not is null" gave an error and customer_id is never null. EDIT it's "is not null", errors if it's the other way around.
it can be null in a left join. That is why you used ou.customer_id != "", right? And I changed the where clause to is not null
IS NOT NULL might not be "correct". If the column is NOT NULL that condition is pointless. It depends on if the column can be NULL and what is used for an empty value. It would help if @ark3typ3 posted the structure of the tables.
@G-Nugget: You are right. ou.customer_id is not on the joined table but on the first one. In that case it can't be null.
This worked, but after some benchmarking I actually did have faster results with a foreach (3 seconds vs 54 seconds). I'm not sure how that was, but since this is a low traffic feature that only admins and executives will see I'm not too worried about dramatic effects on server performance...I'll keep looking into optimizing my queries though because I feel like a few modifications could have lead to much faster performance with the structure I've implemented
|
0

You should be able to do a between statement, which is more efficient.

AND ou.created between '2012-10-08 00:00:00' AND '2012-10-08 23:59:59'

Improve this answer

1 Comment

Thanks for the tip, it shaved off a second, but it's a lot more readable and friendly so I liked it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.