MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:May 11 2002 2:08pm
Subject:RE: MySQL Database Questions
View as plain text  
* florian.wilken@stripped
> 1. Is it a good idea to have all the columns in one big table ?

This will depend on the nature of your data. You say you have many varchar
columns. If many of them contains the same value, you could get substantial
speed/space improvements by splitting (normalizing) the table.

> 2. Does mysql encounter problems with indexes (speed, performance) when
> having large amount of rows ?

No, the problem with big table is when you _don't_ have an index, or when
your query can not use any index. The key is to avoid a full table scan.

> 3. What kind of server configuration (hardware) for the dedicated
> MySQL-Server would you suggest to get a decent performance ?

I don't know much about this, but you should make sure you get enough RAM,
because the indexes can be cached, making queries faster.

> 4. How many concurrent threads/queries can we expect to achieve ?

You said 100 concurrent queries... that should be possible. (The default
value of the server variable 'max_connections' is 100.)

> 5. Shall we look into replicating (master/slave) to get better
> performance

Yes. :)

> ? If yes, how would you suggest a possible setup.

The nature of your application may put some limits on what you can achieve.

One approach may be to have a single master, handling all updates, and
multiple slaves, load balanced to handle all SELECT queries.

> 6. Is char() a better choice than varchar() in terms of speed
> when queries are run (we are aware of the fact that char() requires
> more disc space) ?

Mysql can optimize the use of a table with fixed width. This means _all_
fields must be char/int/date, _none_ can be varchar, text or blob. If this
is not possible, there is no gain in using char over varchar.

Hope this helps, even if my name is not Jeremy. ;)

--
Roger
query

Thread
MySQL Database QuestionsFlorian Wilken11 May
  • RE: MySQL Database QuestionsRoger Baklund11 May