php - Table with over 12 million rows running in performance problems -

-

now table having problems relationship table keyword analysis of websites there 5 columns (keyword_id, website_id, occurrence, percentage, date)

this allows keyword statistics website on period of time , allows visual graph representation website owner.

now problem index 57 unique keywords per website on average. , index 12000 websites everyday , because running performance problems. picture table size growing fast.

now have index on keyword_id, website id, occurrence, percentage , date). each 1 of them has index, still having problems selects.

how solve performance problem on mysql php?

note: indexes each field , 1 of them combined well.  sql query 1: select * table keyword_id = "323242" sql query 2: select * table website_id = "232" sql query 3: select * table keyword_id = "323242" order percentage sql query 4: select * table website_id = "232" order occurence sql query 5: select * table keyword_id = "323242" order occurrence sql query 6: select * table website_id = "232" order date

what's distribution , probability of keywords? example, if had keyword used every site, every day, after 6 mos, that's 2.1m rows single key word. i'm sure that's not case, popular words going large quickly.

the website_id 1 shouldn't bad, few thousand rows.

if you're doing queries keyword_id , website_id, other indexes costing time , space (but not on read).

ideally, index on keyword_id, percentage let optimizer return rather quick result keyword_id sorted percentage query, others, can depend lot of layout of data.

how memory on box , how fast drives? @ io ops per sec when you're doing these queries. can thrashing drives.

with decent amount of memory, order clauses should pretty cheap, , cheaper sort them rather doing lots of random reads disk, that's index , how it's organized in relation pages on disk.

also, make sure have of statistics date. bad statistics murder queries.


Comments

Post a Comment

Popular posts from this blog

objective c - Change font of selected text in UITextView -

-
can suggest me how change font of selected text in uitextview.i cannot use uiwebview since have edit text. have seen pages app.how possible in app. see formatting text within uilabel differently . since need editing, should @ javascript-based editors such ckeditor or tinymce . highly recommend wwdc 2011 video session 511: "rich text editing in safari on ios."
Read more

php - Accessing POST data in Facebook cavas app -

-
so i've got simple form in canvas / iframe facebook app, , i'm trying pass along values post. reading on s.o. , fb's latest docs, understand it, data send via post form can accessed on receiving end $_request object. i read on thread on s.o. in order post forms work need pass along input named "signed_request" value current signed_request (i have signed request working ok otherwise...all login , authentication stuff working fine). isnt mentioned anywhere in official fb docs. so problem comes in $_request object signed request, , bunch of other session stuff. form inputs found. the way can read them set method of form "request" isn't real form method. takes of inputs , sends them args in url. horrible. here's sample page canvas app form i'm using try debug (leaving out authentication stuff): <form enctype="application/x-www-form-urlencoded" method="post" target="_top" id="my_form" action=...
Read more

c# - Getting control value when switching a view as part of a multiview -

-
i have following code in aspx page: <asp:button id="display_button" runat="server" text="display" onclick="button1_click" /> &nbsp; <asp:button id="edit_button" runat="server" text="edit" onclick="button2_click" /> &nbsp; <asp:button id="save_button" runat="server" text="save" onclick="button3_click" visible="false" /> &nbsp; <asp:multiview id="multiview1" runat="server" activeviewindex="0"> <asp:view id="view1" runat="server"> <asp:formview id="view_program" runat="server"> <itemtemplate> <%# eval("status").tostring().trim() %> </itemtemplate> </asp:formview> </asp:view> <asp:view id="view2" runat="server"> ...
Read more