12 {{ upvoteCount | shortNum }}
12 {{ upvoteCount | shortNum }}

How to handle over 300K records with MySQL

by RankSale
10 replies
One of our SEO client is facing a problem to handle their 150K records a table. They have 150K product item in their products table. The worst is they often use left or inner join.

Some said it better to use oracle not mysql to handle over 100K a table.
Companies like Ebay seems also use mysql, are they doing some speical settings?

Our engineer suppose need to seperate the database or just simple upgrade the hardware.

How you think? Any suggestions?
#300k #handle #mysql #records
  • Profile picture of the author metysj
    I guess more info might be needed as to what exactly they do and what is the bottleneck. MySQL is not as bad as many people say. Use pagination+ajax, prepared statements, and/or optimize your queries and you should be fine.
    Signature
    Inspire and engage your fans and followers, effortlessly
    Collaborate with each other to promote your pages and links
    {{ DiscussionBoard.errors[5357637].message }}
  • Profile picture of the author eminc
    Originally Posted by RankSale View Post

    One of our SEO client is facing a problem to handle their 150K records a table. They have 150K product item in their products table. The worst is they often use left or inner join.
    Seems like you need some optimization in the queries as well as
    mysql database tuning. Problem could be at two places, sometimes
    we write queries which take long time, or there are design flaws due
    to which the database is not good enough to handle large amount
    of data. Better consult a Database Administrator for that :-)

    And google is always your friend. If you are facing problems in specific
    situations, you can always google for a tuning option.

    One that explains optimization of sub queries and joins is here:
    How to optimize subqueries and joins in MySQL at Xaprb


    Originally Posted by RankSale View Post

    Our engineer suppose need to seperate the database or just simple upgrade the hardware.
    I think you should check optimizing your existing instance first before
    going for a hardware upgrade. If the problem lies in the query, sooner
    or later you will face the problem once again.

    -Mohit
    {{ DiscussionBoard.errors[5358913].message }}
  • Profile picture of the author jminkler
    Theres absolutely no reason mysql couldn't handle 300k .. this is very small. With the query that you are having a problem with, run "explain select ... from ... where"

    and post the output here or in a pm ..
    {{ DiscussionBoard.errors[5361035].message }}
  • Profile picture of the author infinitewp
    MySQL could handle way more than a million records. The major issue would be

    1) Server bottleneck (Too many mysql connections) / Some software would use 90% of the resources.
    2) The DB should be properly indexed (in terms of Primary key, foreign key etc)
    3) Optimize queries reduce same set of data fetching cache them.

    Thanks
    {{ DiscussionBoard.errors[5388532].message }}
    • Profile picture of the author jasong714
      I recently faced a similar challenge hosting a mysql server on an EC2 instance that was growing too quickly for me to keep up with.

      I came across these guys:

      Cloud Database

      Check them out, scale beyond your imagination They work have both AWS and Rackspace Cloud instances. Lightning fast I/O and solid as a week old donut.
      {{ DiscussionBoard.errors[5409192].message }}
  • Profile picture of the author Maraun
    I don't think a cloud database is the right answer to a simple optimization problem. What they need to do in the first place is profile the app to find bottlenecks, then go about removing them starting with the worst. This may just be a matter of adding an index to a certain column, changing the way queries are written, caching will help a lot too if possible, or maybe even a database redesign to better accommodate the workload profile. For a more specific answer, hire someone to look at it or post more details.
    Signature
    {{ DiscussionBoard.errors[5415393].message }}
  • Profile picture of the author RankSale
    One programmar suggest not to use inner join or left join.
    Instead, do selection for 2 times.
    Like, select the id group first by the first table. and then use IN ( id group) for second time select. Make it in a loop.
    He claims that can reduce the loading of A table x B table (like 5K records x 10K records) select issue.

    How you think? will that reduce the loading of inner join?
    Signature
    CryptoCurrency Market Forums
    {{ DiscussionBoard.errors[5561775].message }}
    • Profile picture of the author jminkler
      Originally Posted by RankSale View Post

      One programmar suggest not to use inner join or left join.
      Instead, do selection for 2 times.
      Like, select the id group first by the first table. and then use IN ( id group) for second time select. Make it in a loop.
      He claims that can reduce the loading of A table x B table (like 5K records x 10K records) select issue.

      How you think? will that reduce the loading of inner join?
      Couldnt say without seeing the explain plan.
      {{ DiscussionBoard.errors[8571808].message }}
    • Profile picture of the author wayfarer
      Originally Posted by RankSale View Post

      One programmar suggest not to use inner join or left join.
      Instead, do selection for 2 times.
      Like, select the id group first by the first table. and then use IN ( id group) for second time select. Make it in a loop.
      He claims that can reduce the loading of A table x B table (like 5K records x 10K records) select issue.

      How you think? will that reduce the loading of inner join?
      Almost surely no. A general rule of thumb is to always let the database do the heavy lifting if you can.

      As pointed out earlier, using indexes is one of the most straightforward ways of optimizing performance, and will work as long as the fields that are being joined are not too large in length. If you're joining on a VARCHAR field there is a defined maximum allowed length depending on which engine you're using. For Innodb it's somewhere around 200 if I remember right, so make sure your varchar fields allow no more characters than you actually need.

      Indexes make the save (insert/update) time a little longer, but unless you have a very write-heavy application, this is not a major concern.

      MySQL :: MySQL 5.5 Reference Manual :: 8.3 Optimization and Indexes
      Signature
      I build web things, server things. I help build the startup Veenome. | Remote Programming Jobs
      {{ DiscussionBoard.errors[8572412].message }}
  • Profile picture of the author seasoned
    I have a couple tables with over a million records, and they work fine. You can't simply define records, join them any which way, and expect them to work. If you do that in the worst way, a database will actually run SLOWER than flat files! Flat files are the fastest way to get the information that you can have. NOTHING can be faster, because they all use it as a base.

    Databases are popular ONLY because they:
    1. simplify changes.
    2. provide an easy standard interface.
    3. handle locking.
    4. have a lot of labor intensive routines that are slow BUT, IF USED RIGHT, can make the access of info appear to be much faster than expected!
    5. they have a few other features, such as network access.

    I could say more, but it would take several pages to cover the basics.

    Steve
    {{ DiscussionBoard.errors[8572356].message }}

Trending Topics