Hi,

I am building a MySQL database, it is probably a daft question but I wondered if the query time would increase by ten fold if the records searched are also increased by 10 fold:

i.e. textual %like% query on 300 records
time taken: 0.31 seconds

Would 3000 take 3.0 seconds and 30000 take 30 seconds?

I have created unique ids for each record insertion and these are used as keys.

Another question regarding the database design is that is it best to put towns, counties, postcodes, countries in different tables to comply with the 3rd normal form within the customer table? As the records could be duplicated ie, county:
Cheshire, on a few records, but not that many.

Converting the database to the 3rd normal form seems to be counter-productive in this case as it is creating more tables and making queries longer, and more difficult, but it does make it 3rd normal form. It even seems more counter-productive on the towns table due to the number of towns within the UK, it is feasible that they may be duplicated but no often.

Please reply if this makes no sense but I am trying to make my
database as efficient as possible, as I am in an early stage in design, but without actually creating it I probably won't know until it is live and too late to make alterations to it.

Thanks
David

Searching 30000 records for matching an integer ID won't take 30 seconds. I hope it will take only a few seconds.

Time consumed in such search depends on how efficiently 'keys' are designed.

MySQL is a very fast daemon.

Yes, PHP is slow. Script, hence should be written efficiently to reduce number of instructions passed to PHP server.

Relational db is a logical structure and also it reduces your database size. Yes it will take extra time to unite/relate tables, but actual problem I see is a) using improper db design and improper keys, ids b) inefficient PHP logic in programming code.

Database shoule be normalized; generally upto 3rd normal form, will solve all the anomalies/redundancy.

Regards,

`~@#\^%&*/\.<.\/-|+|_!:;..=?>
A small donation and a link back to you
Students' finances

Thanks for the reply,

You say I am best to normalize to 3rd normal form but if this only increases query speed and difficulty, why not leave it as 2nd normal form? The table I am referring to is only a customer registration table so the queries are to be likely to be for things like username and mostly textual searches, or maybe postcode?

If you calculate it as 86 counties in the UK (the main source of members for the table) then why add a county table for very little redundancy, and towns even more so, surely this would be better left in the customer table?

Opinions please

David

My suggestion is you put separate table for UK countries and towns. Try to avoid keys that are long.

'zipcode', 'passport number', 'election card number', 'cell number', 'email' can be good keys.

For a set of results search, 'date of birth', 'country', 'town', 'age', 'college name' can be just good. Avoid such keys.

Searching/matching in fields like text can be the only problem.

Normalization will reduce your database size in long run and also your db will look just clean. Yes it get little difficult to write PHP, but I don't think it will delay queries much.

Since you're only asking about 'country' and 'town' tables, these should be separate.

Regards,

`~@#\^%&*/\.<.\/-|+|_!:;..=?>
A small donation and a link back to you
Students' finances

"Converting the database to the 3rd normal form seems to be counter-productive in this case as it is creating more tables and making queries longer, and more difficult, but it does make it 3rd normal form. It even seems more counter-productive on the towns table due to the number of towns within the UK, it is feasible that they may be duplicated but no often."

The thing with 3rd Normal form is to reduce the amount of duplication and hence the amount of data stored, take for example the following. You want to add grid reference to the town, without the 3rd normal form this becomes a new column on the original table, with 3rd normal form it's stored once outside of the table and therefore takes up less space.

The query complexity/length is not relevant, the SQL engine is able to cope and come up with the best execution plan.

Hi thanks for the post, I have indeed elected to go to 3NF, to avoid redundancy as I know it will leave more room for expansion as the database increases in size.

At the time it did seem to be counter-productive but having given it lots of thought it will be best for databases hoping to hold a large amount of data, as the data will become more accurate as it increases in size, after all there are many more people than their are towns!

Thanks

David