MySQL performance.

[Chavo]

I'm writing an application/website that have a shared MySQL database (local to both).  I've abstracted all the mysql queries to their own layer so that my program could easily be switched to another type of backend.  In any case, I'm trying to decide how to implement some of the classes that handle queries.

First Decision:
a) maintain one/few connections to the myssql server using keepalive requests
b) open/close connection for every set of queries (ie open at the beginning of every method, close at the end of every method)

Keeping in mind its on localhost, it seems like option a) would be more efficient, but b) seems to be the correct way if I'm following general IO paradigms.  For some calls, I could be doing 200 transactions in a second or two so I'm hesitant to choose b).


Second Decision:
What is the best way to store arrays of data?  Right now I'm using a varchar that holds a string with space delimitted integers but it seems there has to be a better way.


Third Decision:
a) fewer tables, more columns
b) more tables, fewer columns

does it matter?

Anything other suggestions from mysql design gurus?

[iago]

Make sure you know how to use "indexes" and make sure you set the appropriate type on the appropriate columns.

You should probably look into trying out PostgreSQL, I've heard that it's better at a lot of things. You should run tests with the types of queries you expect to have, and see which is faster.

[Chavo]

Yea, I have primary keys and indexes taken care of.  It doesn't really matter to me which will get results faster for the program.  It does matter, however, how I implement the connection handling in the program to best allow simultaneous interaction from the web server.  Maybe performance was poor word choice

ie, I want to avoid errors, conflicts, etc from 2 seperate sources sharing the same data.  This will be running on a dedicate host that IIRC only has MySQL which is fine with me.

[MyndFyre]

If you're smart about separating your business and data layers, you can probably do something Microsoft calls "optimistic concurrency" by caching your data and then using your cached data in the business layer, persisting back through transactions to avoid rowlocks. 

[Chavo]

Sounds interesting.  Does anyone have any thoughts on the 3 specific choices I described?

[rabbit]

1. I typically go with B, but that's just because it's how I learned to do it.
2. If you're gonna do that, you might want to consider using TEXT instead of varchar.  There are some other methods using UNIONs or JOINs, but they can be messy.
3. This is a hard choice.  It really depends on what kind of information you're storing, as well as its quantity.

[Chavo]

1.  Thats what I see most often in examples, but I've never figured out if there is a reason why.  I can understand why it would be better if the server is not local to the program, so maybe its just consistency.

3.  about half string values, about half integer arrays that I'm storing as strings until I find a better way

[MyndFyre]

Any time where data is repeated you can do a join.

Table: Arrays
ArrayID: pk int identity autoincrement
(other data)

Table: ArrayData:
ArrayID: fk int constrained to Arrays on ArrayID
Value: valuetype

Better to iterate over a rowset than to do a string/varchar split in code IMO.

[Chavo]

I'm not sure I follow.  Your examples don't mean much to me.

[MyndFyre]

[15:11] MyndFyre: so you know how you can have a 1:n relationship between tables based on primary keys?
[15:13] MyndFyre: you can store variable-length arrays by using two tables, one table to store metadata about each unique array and one table (or multiple tables, if the arrays are differently-typed) to store the array elements.  The foreign key table, where the array elements are stored, has the sequence of elements in the array
[15:24] MyndFyre: Anyway, I'm glad we had this talk