Jump to content
Larry Ullman's Book Forums

Mysql, Innodb Engine And Stored Procedures


Recommended Posts

hi

 

This is not a critisism in any way - rather a query regarding table types: MyISAM & INNODB resp. Part two of the good book we get into some advanced MySQL i.e. stored procedures. The procedure (well the first anyway) I'm curious about is:

 

select_sale_items(get_all BOOLEAN)

 

I'm not a MySQL expert by any means so am appealing to the RDBMS gurus really. My question is regarding table locking. What if my database has 1000 records in the sales and non_coffee_products tables say. Then 3000 people log on to my site and all head for the sales page simultaneosly.

 

The tables in the procedure above are of type MyISAM and MyISAM locks tables whilst INNODB locks rows. Will this cause problems in my hypothetical scenario?

 

I ran

get_sale_items(true)

at home with six records in the database and it executes in (0.00) sec, fast! Would a production version differ significantly?

 

Would the query make it to the MySQL slow-query.log with a slow query being > 1 sec?

 

:)

Link to comment
Share on other sites

Well, 1,000 sale items would be quite something. If that was the case, I would change the procedure and the site to only select a subset of sale items at all times.

 

Whether you use MyISAM or InnoDB really depends upon the version of MySQL and the OS and the type of queries most frequently being run. Would a production server with lots of concurrent requests differ significantly? Of course. Would the query make it to the MySQL slow-query.log? Yes, if it ran slowly!

 

You're asking for definitive answers to hypothetical questions, which I'm not capable of answering. The definitive answers will depend upon so many factors: the hardware, the network, the types of other requests being made at the same time, etc., etc. If you're fortunate enough to have a site with 3,000 concurrent users, you'll need to do a lot of research and experimentation to fine tune every aspect of the server to perform optimally.

  • Upvote 1
Link to comment
Share on other sites

Too many variables for a definitive answer, that makes sense. I was being ambitious with 1000 sale items and 3000 users; that's nearly Amazon material! I suppose the best way to find out would be to deploy it, then watch the .log files for bottlenecks as the site evolves. Thanks for the insight.

Link to comment
Share on other sites

 Share

×
×
  • Create New...