SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Looking for ways to speed up SELECTs from a 2.4 GB table

    Unfortunately, I really need the data in this table so I can't dump the old stuff and I can't change the structure of the DB too much right now due to how important this data is and how many different apps access it. I have one page that queries this db several times (different DISTINCT fields I need to gather). The table has over 13 million rows and is growing daily by a few thousand.

    What I am asking is what steps I can take to help MySQL process the SELECTs on this table quicker.

    The system has 2GB of RAM. There is no overhead on the tables. MySQL version is 4.0.x. I am doing over 41,000 SELECTs per hour, although only this table is slow.

    The create table statement:

    Code:
       CREATE TABLE `ipstats` (
         `ip` varchar(16) NOT NULL default '',
         `month` int(2) default NULL,
         `year` int(4) default NULL,
         `city` varchar(25) NOT NULL default '',
         `region` varchar(25) NOT NULL default '',
         `page` int(8) NOT NULL default '0',
         `lastdaytime` varchar(20) default NULL,
         `param` varchar(255) NOT NULL default '',
         KEY `ip` (`ip`),
         KEY `month` (`month`),
         KEY `year` (`year`),
         KEY `region` (`region`),
         KEY `page` (`page`),
         KEY `param` (`param`)
       ) TYPE=MyISAM;
    my.cnf:
    Code:
       set-variable=key_buffer_size=256M
       set-variable=sort_buffer_size=384M
       set-variable=record_buffer=1M
       set-variable=net_buffer_length=32768
       set-variable=innodb_buffer_pool_size=64M
       set-variable=myisam_sort_buffer_size=384M
       set-variable=max_connections=500
       set-variable=table_cache=256
       set-variable=query_cache_size=256M
       set-variable=query_cache_limit=1M
       set-variable=query_prealloc_size=32768
    Thanks for any suggestions you have.

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are your most common queries on the table?

    Mike

  3. #3
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MikeBigg
    What are your most common queries on the table?

    Mike
    Here are all the sql statements that are loaded from the most used web page.

    Code:
       SELECT DISTINCT ip
       FROM ipstats
       WHERE page = $rid
       AND month = $i
       AND year = $year
       AND ip IN ($searchengines)
    ($searchengines is a comma-delimited list of about 1500 ip addresses)
    Code:
       select count(distinct ip) from ipstats where year='$year' and page=$rid and month='$ThisMonth'
    Code:
       select count(distinct ip) from ipstats where year='$year' and param like ',$parentparams,%' and month='$ThisMonth'
    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do you have any queries which need to see results for a specific month over a number of years?

    if not, you might replace the two separate indexes on year and month with a composite index on (year,month)

    the LIKE query isn't going to work the way you think, unless the values in the param column always start with a comma
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    do you have any queries which need to see results for a specific month over a number of years?
    I do need to see results for specific months over several different years from a couple of apps, although they aren't run nearly as often.

    Quote Originally Posted by r937
    the LIKE query isn't going to work the way you think, unless the values in the param column always start with a comma
    Yes, they always start with a comma.

  6. #6
    SitePoint Zealot newspire's Avatar
    Join Date
    Mar 2005
    Location
    Houston, TX
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can leave the index on the month for the other application and change the year index to year,month,page,ip. Then for a couple of your queries MySQL will not even have to look at the data file it will simply use the index.

    Note: it is going to take a while to reindex that baby.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    covering index -- great idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot newspire's Avatar
    Join Date
    Mar 2005
    Location
    Houston, TX
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, I didn't know that had a name. You learn something new every day on SitePoint!


Bookmarks

Posting Permissions

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