SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help with performance of table with 200k records

    My client is experiencing severe performance issues going through a sales process. the order/cart table has around 200,000 records and is modified as you move through the process. Performance for multiple item orders takes a minutes to refresh.

    Their server version is 4.0 and the table has a primary index. (I am moving it to at least version 4.1)

    It is running PHP as the scripting language and I am going through logs to see what's up there as well but the performance issues have been ongoing and getting worse so it leads me to believe it's a db issue.

    Any ideas on improving performance?
    Last edited by WebDevGuy; Feb 15, 2007 at 10:06.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please post the create table statements.

    also, look at the slow query logs.

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CREATE TABLE `purchase_list` (
    `purchase_list_id` int(10) unsigned NOT NULL default '0',
    `purchase_id` int(10) default '0',
    `menu_id` int(10) unsigned NOT NULL default '0',
    `purchase_list_price` float NOT NULL default '0',
    `purchase_list_specific` varchar(128) default NULL,
    `purchase_list_name` varchar(255) NOT NULL default '',
    `user_id` int(10) default NULL,
    `old_purchase_id` int(10) default NULL,
    `purchase_mod_date` datetime default NULL,
    PRIMARY KEY (`purchase_list_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    longneck, your giraffe favicon is looking a little frazzled there - are you over-worked these days?

    Would manually re-indexing help? I am considering archiving part of the data on the largest tables to see if that makes a difference but on a production machine, it's tricky.

    Any experiences/advice is much appreciated!
    Last edited by WebDevGuy; Feb 15, 2007 at 13:43.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you probably join to this table on the purchase_id and user_id columns frequently, right? if so, you need to create indexes on those column.

    if you also post a specific query that is slow and the output of EXPLAIN, i can probably provide stronger advice.

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks - let me give that a try before taking more of your time.

    Many thanks!

  6. #6
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    200k is nothing, got tables with over 200mill records returning 0.01 sec for most queries.

    What you need to do, is check all your sites queries that are using that table, and see what your searching on. Then index those fields. (Read up on indexes, since if you do it in a retarded way, then it won't work right...)

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am starting to think it's the php - but will go over previous developer's code & queries

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    have you looked at the slow query log?

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you looked at your server's performance to see if it's the mysql server or the apache that is demanding all of the resources? I don't think a query should take a minute to run -- especially if it's just a couple inserts w/ 1 index.
    MySQL v5.1.58
    PHP v5.3.6

  10. #10
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's being hosted and they won't give me access to their mysql logs and I can't see server performance either.

    After more reflecting, it makes perfect sense to add indexes to related tables. I work deeply with MySQL about two week every two months and I tend to forget the obvious. :-)

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

  12. #12
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by WebDevGuy View Post
    it's being hosted and they won't give me access to their mysql logs and I can't see server performance either.
    In a case like that (if switching hosts isn't an option), I'd make the host tell me which is slower. It's in the host's best interest to help you troubleshoot performance.

    bookmarked!
    MySQL v5.1.58
    PHP v5.3.6


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
  •