Application optimization question

[jbeninger]

Hi all. I'm on about my third database application and I've started thinking about optimization. At the moment, this question is academic, as the database is small and either of my two alternatives will work equally well. I just want to better understand how to optimize. Basically my question is whether a long lock or a repeated query is worse, as in the following example (pseudocode):

1)a) LOCK TABLES 1,2,3

b) SELECT <6 columns from 3 tables> WHERE
table1.user=table2.user AND table1.some_id = table3.some_id;

c) For each result, do an update on tables 1, 2, 3. These updates only affect one row per table.

d) UNLOCK TABLES


2)a) Select user, some_id FROM table1;

b) for each result:
- LOCK TABLES
- Select the same information as in 1b, but for a single row from each table (the row is identified by the user and id information from step 2a)
- Update information as in 1c
- UNLOCK TABLES


Basically the difference between the two is that in the first one, I select all of the information I need at once, but have to leave the tables locked the whole time. In the second one, I have to do an extra select query in the loop, but the tables are locked/unlocked more often.

Thanks,
James Beninger

 

[swampBoogie]

You shouldn't use lock/unlock but rather group the related operations within a transaction.

 

[jbeninger]

Ideally yes, but my database doesn't support transactions.

 

[swampBoogie]

Newer versions of Mysql do support transactions when using certain tablehandlers.

In general,especially with many simultaneous users, the time a table is locked should be kept to a minimum. In this aspect 2 is preferable. But on the other hand, if the operations should be done as one operation you need to use 1. Functionality exhausts performance.

 

[jbeninger]

Ah - one of those &quot;it depends&quot; things - I gotcha. Thanks for the help.

James