MySQL Forums
Forum List  »  Newbie

Re: Are queries queued?
Posted by: Martin Gavin
Date: May 18, 2009 03:35PM

Hi Rick,

Thanks for you reply! Answers below.

* MyISAM ignores BEGIN/COMMIT. The alternative is

LOCK TABLES WRITE
SELECT id FROM products WHERE status = 'available' LIMIT 3;
UPDATE products SET status = 'pending', timestamp = now()
WHERE id = #the 3 rows in the previous query#
UNLOCK TABLES;

#####
##### Would the LOCK TABLES stop any selects etc from being run on that table while locked?


* Did you want an ORDER BY on the SELECT, or just get a random 3?
#####
##### The products can be any of the selected type, the products table will be a live stock table, for example there will be 1000 of the same products added so the 3 selected can be any 3 of the selected type.

* If you want a particular 3, do you have an appropriate index?
#####
##### I currently have no index, I am not familiar with using them but I think I need one in this case. Could you recommend which fields to set it up on?

* If you want a truely random 3, use ORDER BY rand() -- but that will seriously slow things down.
#####
##### Do not need this.

* WHERE id = ... probably needs to be WHERE id IN (...)
#####
##### Yeah, how can I pass the result from the first query into the WHERE IN (result of 1st query)?

* If you switch to InnoDB, look up "FOR UPDATE";
#####
##### Have just looked this up, this answers my previous question.

* MyISAM can probably do the suggested code sequentially in 1/100th second. So it might take 10 seconds. Is that good enough?
#####
##### Would it really take that long return 3 rows and update them? Maybe we have our wires crossed?

* I have trouble imagining an application with 1000 connections running the same query in the same second.
#####
##### If you can imagine a first come first served sale opening at 1pm on a certain day with a lot of people waiting for 1pm to come so they can buy at once before the products are gone?

* With InnoDB, you would need to respond to failures, especially deadlocks. This is very likely to occur when you have the described contention.
#####
##### Would lock, commit, unlock take care of this?

As requested:

CREATE TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `supplier_e` int(10) NOT NULL default '0',
  `color_b` int(10) NOT NULL default '0',
  `name` int(50) NOT NULL default '0',
  `make_r` int(11) NOT NULL default '0',
  `cost_type` int(11) NOT NULL default '0',
  `status` tinyint(4) NOT NULL default '0',
  `user_updated` int(11) NOT NULL default '0',
  `date_updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_added` int(11) NOT NULL default '0',
  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4001 DEFAULT CHARSET=latin1

"products"	"MyISAM"	"9"	"Fixed"	"4000"	"50"	"200000"	"214748364799"	"34816"	"0"	"4001"	"2009-05-13 21:05:22"	"2009-05-13 21:30:00"	\N	"latin1_swedish_ci"	\N	""

SELECT id 
FROM products 
WHERE supplier_e = 1 && 
color_b = 1 && 
cost_type = 1 && 
status = 1
LIMIT 3

Hope my answers help you.

Martin

Options: ReplyQuote


Subject
Written By
Posted
May 15, 2009 05:32AM
May 15, 2009 01:27PM
May 15, 2009 02:50PM
May 16, 2009 07:19PM
May 16, 2009 07:26PM
May 18, 2009 03:38PM
Re: Are queries queued?
May 18, 2009 03:35PM
May 18, 2009 11:16PM
June 03, 2009 02:37PM
June 03, 2009 07:40PM
June 05, 2009 01:35PM
June 05, 2009 08:47PM
June 06, 2009 11:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.