SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Query speed

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    London
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query speed

    Hi,

    I've populated a table with 100.000 records of data and a single query to select a record based on two parameters (indexed fields) take up to 2 seconds on my office machine. Is this normal?

    Customers need to be searched for type and flag and then sorted by lastvisit and limited by 10. It doesn't matter what is the limit, one search takes around 2 seconds. Is there something obvious I'm doing wrong?

    Code MySQL:
    CREATE TABLE `customers` (
      `id` int(11) NOT NULL auto_increment,
      `customernumber` varchar(13) default NULL,
      `name` char(255) default NULL,
      `address` char(255) default NULL,
      `telephone` char(128) default NULL,
      `type` char(255) default NULL,
      `notify` tinyint(4) default NULL,
      `productsviewed` int(11) default NULL,
      `flag` char(1) default NULL,
      `purchases` char(4) default NULL,
      `lastvisit` bigint(20) default NULL,
      PRIMARY KEY  (`id`),
      KEY `lastvisit` (`lastvisit`),
      KEY `type` (`type`),
      KEY `flag` (`flag`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=102509

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    only one index can be used at a time so it either uses type or flag, make it a composite index
    Code:
    KEY bothofem (type, flag)
    and see if that helps

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    London
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    only one index can be used at a time so it either uses type or flag, make it a composite index
    Code:
    KEY bothofem (type, flag)
    and see if that helps
    Amazing! Now instead of 2 seconds per result set, I'm getting 200 result sets per second! I thought at first it was 20 per second and I was very impressed... then I looked at the numbers better.

    Well I guess I learned something very useful today. Only one key is used so if I search by more parameters I need to make a composite key. Thanks a lot!

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    London
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another question - I'm getting only 22 inserts per second. Is that normal? It used to be 30 and now it's slower because of the big index, but are those numbers normal?

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    indexes speed up selects and slow down inserts and updates, it is a trade off.

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how are you attempting to do inserts? are you aware that you can store your data in a file and then use LOAD DATA INFILE to import the data significantly faster than any other way you can get data into your table?

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    London
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    how are you attempting to do inserts? are you aware that you can store your data in a file and then use LOAD DATA INFILE to import the data significantly faster than any other way you can get data into your table?
    Yeah but inserts will be inserted by hand from 20-30 client machines so I wanted to see how that will work. Still even at an estimated (and overly optimistic) 30 seconds per insert I'd say there won't be any trouble. I know there won't be any trouble with selects - 200 per second, wow! - all on a server that can be put together for $150.

    Thanks for your help

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    London
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    An update to this thread.

    On Windows XP with MySQL 5 + PHP 5 + Apache 2 I was getting around 30 inserts per second and around 200 result sets per second.

    On Linux Mint 6 with MySQL 5 + PHP 5 + Apache 2 I'm now getting around 450 inserts per second and around 2000 result sets per second.

    The hardware is Athlon X2 4000+ with 3 gig RAM and WD Caviar 80 gig disk.

    Is there something wrong with my Windows XP or is this a normal difference between WAMP and LAMP setups?

  9. #9
    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)
    more likely had to do with you mysql configuration. but in general, you'll get better performance from a linux box when running mysql. if both are finely tuned and compared, the difference should be 5-20%, not 1000%.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •