Performance - order by in MySQL or in PHP -


i know has been asked before @ least in thread: is php sort better mysql "order by"?

however, i'm still not sure right option here since performance on doing sorting on php side 40 times faster. mysql query runs in 350-400ms

select  keywords id,  sum(impressions) impressions,  sum(clicks) clicks,  sum(conversions) conversions,  sum(not_ctr) not_ctr,  sum(revenue) revenue,  sum(cost) cost  visits campaign_id = 104 group keywords(it's integer) desc

keywords , campaign_id columns indexed.

using 150k rows , returns around 1500 rows in total. results recalculated (we calculate click through rates, conversion rates, roi etc, totals whole result set). calculations done in php.

now idea store results php apc quick retrieval, need able order these results columns calculated values, therefore if wanted order click-through rate i'd have use (sum(clicks) / (sum(impressions) - sum(not_ctr)) within query makes around 40ms slower , initial 400ms long time already.

in addition paginate these results, adding limit 0,200 doesn't affect performance.

while testing apc approach executed query, did additional calculations , stored array in memory executed once during initial request , worked charm. fetching , sorting array memory took around 10ms, script memory usage 25mb. maybe it's worth loading results memory table , querying table directly?

this done on local machine(i7, 8gb ram) has default mysql install , production server 512mb box on rackspace on haven't tested yet, if possible ignore server setup.

so real question is: worth using memory tables or should use php sorting , ignore ram usage since can upgrade ram? other options consider in optimizing performance?

in general, want sorting on database server , not in application. 1 reason database should implementing parallel sorts , has access indexes. general rule may not applicable in circumstances.

i'm wondering if indexes helping you. recommend try query:

  1. with no indexes
  2. with index on campaign_id
  3. with both indexes

indexes not useful. 1 particularly important factor called "selectivity". if have 2 campaigns in table, better off doing full-table scan rather indirectly searching through index. because particularly important when table not fit memory (resulting in condition every row requires load page cache).

finally, if going application expands beyond single server, careful. optimal on single machine may not optimal in different environment.


Comments

Post a Comment

Popular posts from this blog

python - How to create a legend for 3D bar in matplotlib? -

Image
given ax = plt.subplot() : ax.bar()[0] can passed plt.legend() . however, ax.bar3d() returns none . how create legend displayed bars? update: passing legend="stuff" ax.bar3d() , calling ax.legend() raises /usr/lib/python2.6/site-packages/matplotlib/axes.py:4368: userwarning: no labeled objects found. use label='...' kwarg on individual plots. warnings.warn("no labeled objects found. " you need use proxy artist legends not supported. this code: from mpl_toolkits.mplot3d import axes3d import matplotlib.pyplot plt import numpy np fig = plt.figure() ax = fig.add_subplot(111, projection='3d') x, y = np.random.rand(2, 100) * 4 hist, xedges, yedges = np.histogram2d(x, y, bins=4) elements = (len(xedges) - 1) * (len(yedges) - 1) xpos, ypos = np.meshgrid(xedges[:-1]+0.25, yedges[:-1]+0.25) xpos = xpos.flatten() ypos = ypos.flatten() zpos = np.zeros(elements) dx = 0.5 * np.ones_like(zpos) dy = dx.copy() dz = hist.flatten() ax...
Read more

java - Multi-Label Document Classification -

i have database in store data based upon following 3 fields: id, text, {labels}. note each text has been assigned more 1 label \ tag \ class. want build model (weka \ rapidminer \ mahout) able recommend \ classify bunch of labels \ tags \ classes given text. i have heard svm , naive bayes classifier, not sure whether support multi-label classification or not. guides me right direction more welcome! the basic multilabel classification method one-vs.-the-rest (ovr), called binary relevance (br). basic idea take off-the-shelf binary classifier, such naive bayes or svm, create k instances of solve k independent classification problems. in python-like pseudocode: for each class k: learner = svm(settings) # example labels = [class_of(x) == k x in samples] learner.learn(samples, labels) then @ prediction time, run each of binary classifiers on sample , collect labels predict positive. (both training , prediction can done in parallel, since problems assumed ...
Read more

php - Dynamic url re-writing using htaccess -

how can rewrite http://mydomain.com/test/master.php?catid=2&subcatid=8 http://mydomain.com/test/master.php/catname/subcat using .htaccess file. once rewritten, how can access these parameters in master.php? note: untested, let me know if doesn't work. rewriterule ^master.php/([a-za-z0-9_-]+)/([a-za-z0-9_-]+)$ /master.php?catid$1&subcatid=$2 [l]
Read more