Databases
StackOverflow2013
Postsdbo
POSimple but heavy application consuming a lot of resources. How to Optimize?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSimple but heavy application consuming a lot of resources. How to Optimize?
    primarykey
    data
    text
    <p>Currently I have one monitor application in production. The job of this monitor is to collect specific entries on social networking like facebook, twitter, youtube and so on. </p> <p>Here are one simple example of an API call from Twitter:</p> <p><a href="http://search.twitter.com/search?q=Stackoverflow&amp;format=json" rel="nofollow">http://search.twitter.com/search?q=Stackoverflow&amp;format=json</a></p> <p>Basically, this is what the system does:</p> <ol> <li>Select the search term from database given an specific order</li> <li>Call the API</li> <li>Collect all tweets statuses IDs and users IDs from the current search</li> <li>Check on the database if it exists</li> <li>Run the tweets insertion eliminating existing tweets and users and preventing duplicated entry errors.</li> </ol> <p>We finished with two tables, one for users and another for tweets.</p> <p><strong>THE PROBLEM</strong></p> <p>After the MySql database reached 200.000 entries on the tweets table (on the first months), the application that visualize that data started to consume too much resources when performing the select query on the existing tweets.</p> <p><strong>Why?</strong></p> <p>The system has separated accounts, each one has certain search terms related to their specific business. When we perform a select, we need to select only the ones that are associated with the terms of our account. We cannot see tweets the aren't related to us. But one tweet can be on many accounts.</p> <p><strong>The actual query (Hurting my eyes)</strong></p> <pre><code>SELECT * FROM tweets WHERE content LIKE '%searchterm1%' OR LIKE '%searchterm2%' OR LIKE '%searchterm3%' OR LIKE '%searchterm4%' OR LIKE '%searchterm5%' OR LIKE '%searchterm6%' OR LIKE '%searchterm7%' OR LIKE '%searchterm8%' (and son on...) </code></pre> <p><strong>The two possible solutions</strong></p> <p>a) Create a tweets_searches table which will have two foreign keys: tweet_id and search_id. So that each tweet on the tweets table can be related to one specific search term.</p> <p>So instead of search for a specific string, we will join these tables.</p> <p>b) Continue searching, but instead, with fulltext searches using <code>MATCH () AGAISNT ()</code>.</p> <p><strong>THE MAIN QUESTION</strong></p> <p>Is that enough to reduce the resources consumption like CPU and RAM? Is there anything better I can to do?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKeyne Viana
    1. USKeyne Viana
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSimple but heavy application consuming a lot of resources. How to Optimize?
      1. USerisco
      1. VTFavorite
    2. VO
      singulars
      1. POSimple but heavy application consuming a lot of resources. How to Optimize?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSimple but heavy application consuming a lot of resources. How to Optimize?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy not use `match ... againts`?
      singulars
      1. POSimple but heavy application consuming a lot of resources. How to Optimize?
      1. USNemoden
    2. COOr you could use `Sphinx` - would be even better.
      singulars
      1. POSimple but heavy application consuming a lot of resources. How to Optimize?
      1. USNemoden
    3. COa full text search would be far more efficent
      singulars
      1. POSimple but heavy application consuming a lot of resources. How to Optimize?
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload