Results 1 to 8 of 8
  1. #1
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14

    Unanswered: How to handle too many rows in mysql server

    I have a classifieds site uses MySql as database server in which thousands of new ads are posted and each ad will be tagged with few tags. Now when I store each tag in a separate row along with the ad reference id, I could see about 3 to 4 thousands of rows are added to the table which is increasing load heavily day by day.

    Here is the example of storing tags in a table.
    postid tagname
    1 real-estate
    1 homes
    1 best-homes
    2 job
    2 internet-jobs
    2 workfromhome

    Is there any way to decrease the load on the server. Can I split table into parts and store in different servers and use them as a single table when the query is executed?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what efforts have you made to optimize your queries with appropriate indexes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14
    Quote Originally Posted by r937 View Post
    what efforts have you made to optimize your queries with appropriate indexes?
    I have only 2 columns(postid tagname) in the table and I created a Boolean index on postid.
    Last edited by vibhavram; 09-25-11 at 06:55.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vibhavram View Post
    I have only 2 columns(postid tagname) in the table and I created a Boolean index on postid.
    please do a SHOW CREATE TABLE on the table, and give an example of a search query that uses this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14
    Quote Originally Posted by r937 View Post
    please do a SHOW CREATE TABLE on the table, and give an example of a search query that uses this table

    I used following query to create table

    CREATE TABLE `draggin_classifieds`.`cfd_tags` (
    `postid` BIGINT NOT NULL ,
    `tagname` VARCHAR( 128 ) NOT NULL ,
    INDEX ( `postid` )
    ) ENGINE = InnoDB;

    Now when I wnat to retrieve all the posts with a tagname I am using

    SELECT * FROM cfd_tags WHERE tagname='sometag' LIMIT 0, 20;
    Last edited by vibhavram; 09-25-11 at 07:18.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that cannot possibly be the correct table layout, because if postid is the primary key, then there can be only one row for each postid, however, your example says you have multiple rows per postid like this --

    1 real-estate
    1 homes
    1 best-homes
    2 job
    2 internet-jobs
    2 workfromhome

    so something is wrong, you have misrepresented something, and i can't help you until you clear this up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14
    Quote Originally Posted by r937 View Post
    that cannot possibly be the correct table layout, because if postid is the primary key, then there can be only one row for each postid, however, your example says you have multiple rows per postid like this --

    1 real-estate
    1 homes
    1 best-homes
    2 job
    2 internet-jobs
    2 workfromhome

    so something is wrong, you have misrepresented something, and i can't help you until you clear this up
    I am sorry. I corrected the create table query above.

    Here is that

    CREATE TABLE `draggin_classifieds`.`cfd_tags` (
    `postid` BIGINT NOT NULL ,
    `tagname` VARCHAR( 128 ) NOT NULL ,
    INDEX ( `postid` )
    ) ENGINE = InnoDB;

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the index on postid, then run the following two statements--
    Code:
    ALTER TABLE cfd_tags
    ADD PRIMARY KEY ( postid , tagname )
    Code:
    ALTER TABLE cfd_tags
    ADD INDEX ( tagname, postid )
    then try your query again and see how fast it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •