I am not sure why this query is taking 8 seconds to load. There is about a million records in link_feeds. There are indexes on: with cardility ID PRIMARY 1193287 rss_id INDEX 5473 pub_date INDEX 596643 hits INDEX 389 titleindx INDEX 1193287 feed_url INDEX 1193287 EXPLAIN SELECT id, feed_title, feed_detail, feed_image, pub_date FROM link_feeds WHERE rss_id =2 ORDER BY id DESC LIMIT 25 [ Edit ] [ Skip Explain SQL ] [ Create PHP Code ] id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE link_feeds ref rss_id rss_id 2 const 4105 Using where; Using filesort I am not sure, your help is greatly appreciated thanks!
Try go to the PHPMyAdmin, Select all table, then choose the "optimize table" This will help you a lot by optimizing the table
tried it took a while so it seemed to do something, but still getting slow 8 second for that query... Thanks!
Normally poor usage of indexes could cause lag like this. The database is large but it not monstrous by any means. The problem that I see with the query in general is that you are sorting a massive number of potential records to extract 25. The database has to sort the entire record set before it can take off the top 25, so there is a lot of overhead to get those few records. Adjusting sort_buffer_size may help in this case as well. If you can, try to add more conditionals on indexes to get the initial sort down do a reasonable size. Also, is there a specific reason you need that many indexes? If you use them all, that's fine, but they take up a lot of resources, so if not needed, they should definitely be removed especially the text based ones.
Thanks a ton for you help, hoping I can figure this out, btw it is a dedicated dual xeon with 2gb of ram... sort_buffer_size is current at 2M... Little more info: $res1=mysql_query('select id, feed_title, feed_detail, feed_image, pub_date from link_feeds where rss_id=' . $id . ' order by id desc' . $nrec . ''); which query turns into: select id, feed_title, feed_detail, feed_image, pub_date from link_feeds where rss_id=249 order by id desc limit 0,8 On first load on page I get slow time... after $res1=mysql_query('select id, feed_title, feed_detail, feed_image, pub_date from link_feeds where rss_id=' . $id . ' order by id desc' . $nrec . ''); = Processed:0.393Memory:389920 other queries, few diff stuff after, final total loading time for box all the way through the while($row1=mysql_fetch_assoc($res1)) with above time is = Processed:0.395Memory:389920 ---- there is about 16 diff boxes so it matters... If you refresh the page they all load in .001...I do the query by itself always get .001 or less.... so only way to add more conditionals, there really is nothing else to narrow it down when just listing last 25 from records....I suppose I could add some more columns to narrow it down...would this help? Its not sorting a massive record set...without the limit 0, 25 is: Showing rows 0 - 29 (4,689 total, Query took 0.0242 sec) SQL query: SELECT id, feed_title, feed_detail, feed_image, pub_date FROM link_feeds WHERE rss_id =6 ORDER BY id DESC LIMIT 0 , 30 Indexes-------- pub_date INDEX 596643 hits INDEX 389 titleindx INDEX 1193287 feed_url INDEX 1193287 those are all, I have select < pubdate, sort by hits, then matching title and urls...those all are ok uses for indexes correct? Thanks in advance!
Hi puffyz, Tuning the sort_buffer_size might improve performance for you. Check out this link: http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql/ In this case, reducing the size of the sort_buffer_size improved performance. Petey