Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

The following table is part of my project. I'm really strict about field types and store engines. I've read a lot of questions from this site, but I think there are a lot to do in the first step. Is there anything wrong with below fields and/or their relationship? Thanks in advance.

http://i.stack.imgur.com/AT1Ie.jpg

share|improve this question
MyISAM engine does not support foreign keys. I guess you must be using InnoDB table type. small integer may not be the right datatype. Consider INT. The logical flow seems to be OK. – shantanuo Dec 15 '11 at 14:22
tnx, the problem with InnoDB is that the store engine does the select queries slower than MyISAM (ofcourse not in all cases), and select is really important for me. I have a lot of select statements. – john.locke Dec 15 '11 at 15:11
"InnoDB selects are slower than MyISAM" and alligators live in NY sewers: bigsiteofamazingfacts.com/… – ypercube Dec 16 '11 at 13:42

1 Answer

up vote 1 down vote accepted

Based on the comments to your question, I would strongly recommend using InnoDB since it is ACID complaint and supports your foreign key layout.

When it comes to using InnoDB:

  • Every working table needs to be InnoDB.
  • You cannot use InnoDB in its Default setttings. You must tune its disk and memory settings.
  • Every query doing JOINs cannot have a mixture of InnoDB and MyISAM or every SELECT query deteriorates with full tables locks : http://dba.stackexchange.com/a/9222/877
  • You must give InnoDB a big enough Buffer Pool so data and index pages are cached : http://dba.stackexchange.com/a/2194/877
  • You need to have InnoDB tables dwell in their own tablespace files or ibdata1 (default tablespace file) will grow out-of-control : http://dba.stackexchange.com/a/8983/877

As for the log table you have that uses the ARCHIVE storage engine, keep in mind

  • ARCHIVE storage engine has no indexes
  • Only INSERTs and SELECTs allowed
  • All SELECTs are full table scans every time

As for SELECTs, you must tune the SELECTs to take proper advantage of table indexes. Otherwise, add the appropriate indexes to the tables.

share|improve this answer
hi, I always learn new things from your posts, tnx. actually Archive SE support one and only one index. I can use LIMIT to prevent full table scan. If I'm wrong please correct me. It is appreciated. – john.locke Dec 16 '11 at 3:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.