Re: Performance issues when the number of records are around 10 Million

On 11 May 2010 10:18, venu madhav <venutaurus539@gmail.com> wrote:
> select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name,
> e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1,
> e.wifi_addr_2, e.view_status, bssid  FROM event e, signature s WHERE
> s.sig_id = e.signature   AND e.timestamp >= '1270449180' AND
> e.timestamp < '1273473180'  ORDER BY e.cid DESC,  e.cid DESC limit 21
> offset 10539780;
>
> Can any one suggest me a better solution to improve the performance.
> Please let me know if you've any further queries.

1. Avoid using large OFFSETs. Do instead "... ORDER BY e.cid, e.cid LIMIT 21;"
2. What "EXPLAIN SELECT ..." shows?
3. What "\d event" prints?

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802