Query speed

[A-OK]

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?

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

[guelphdad]

only one index can be used at a time so it either uses type or flag, make it a composite index

KEY bothofem (type, flag)

and see if that helps

[A-OK]

only one index can be used at a time so it either uses type or flag, make it a composite index

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!

[A-OK]

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?

[guelphdad]

indexes speed up selects and slow down inserts and updates, it is a trade off.

[guelphdad]

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?

[A-OK]

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

[A-OK]

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?

[longneck]

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%.