CODEWITHSUNDEEP
mysqldatabaseoptimizationdatabase-performance
Umer
Umer

Reputation: 133

Very Slow Query in MySQL

I have a scenario like;

I have a table named "tbl_gust_comb_archve_01nov11_beyond"

Indexed keys are set on these fields "Gid, gip, siteid, kw, kwtype, dt, gpage, dated"

And

This is my query:

SELECT SQL_CALC_FOUND_ROWS 
  gid, gip, siteid, kw, kwtype, dt, count(id) as vpage, sum(mapped) as mapped 
FROM 
  tbl_gust_comb_archve_01nov11_beyond 
WHERE 
  confirmation = 1 
AND 
  dated BETWEEN '2012-01-31' AND '2012-01-31' 
AND 
  siteid = 'bing' 
GROUP BY gid 
ORDER BY dt 
DESC LIMIT 0,50

If you make the date a RANGE such as '2012-01-31' AND '2012-02-01' then the result will take longer then 10-30 minutes.

If you have a date range and REMOVE the "GROUP BY" then the result will be much faster (about 5 minutes). Though! after removing GROUP BY, 5 minutes are also too much...

Table size is "30mill records and 12Gig".

Thanks!

Upvotes: 1

Views: 262

Answers (2)

Tadeck
Tadeck

Reputation: 137320

You should first do EXPLAIN on the query, as Mark Baker suggested within his comment.

But probably creating a multi-column index on these columns should solve the problem:

  • dt (this probably should be the first)
  • confirmation
  • dated
  • siteid
  • gid

I am not sure how the gid should be indexed (on which position, etc.).

More details are here, so you can decide on the solution on your own:

Upvotes: 1

Coren
Coren

Reputation: 5637

If siteid does not vary a lot, you can try to remove your index on siteid. If you have 30mill. record and 1/3 with siteid == "bing", then your query will

  1. Grab those 10mill. record
  2. Apply your dated lookup afterwards, on those 10 mill. records, which can be really slow.

It's quite logical, since selecting a range is, normally, longer than selecting a simple value. If siteid does vary a lot, you can try to add a dual index on both dated & siteid.

For confirmation field, since you are in a archive table, maybe you can move those who have not confirmed to an other table. You can also gain some speed if you are able to remove this check.

Upvotes: 0

Related Questions