Hi! Please post the actual SHOW CREATE TABLE statements for the tables
in question. Thanks!

Jay

On Mon, 2006-09-18 at 12:03 +0500, Asif Lodhi wrote:
> Hi,
>
> I have a multi-column index (TrnsxType, TrnsxDate, TrnsxID, DepartID).
> This index along with
> a multi-column index of some child tables results in 8-column indexes
>
> (TrnsxType, TrnsxDate, TrnsxID, DepartID, OrderType, OrderDate,
> OrderNo, DepartmentID), etc.
>
> I cannot eliminate Department ID because the software has to go with
> the manual procedures where each department has its own
> order/invoice/bill books - each with its own number series.
> In case the software goes down because of a power failure or
> something, things will continue on the manual system - using the
> manual system's document numbers, etc. When the power becomes
> available, transactions manually recorded will be fed into the
> software-based system.
>
> To cope with the very likely possibility of duplication of manual
> order/invoice numbers etc. with those of software generated
> invoice/order numbers, etc., I am storing invoice/order numbers
> recorded on manual invoices/orders as Negative numbers in the database
> - so that even if there is a duplication, the two numbers stay
> separate - yet to the physical paper world they stay the same - well,
> almost - differing only in the signs! However, even in that case, I
> have a problem - since there can be power/network failures, I am
> storing document (invoice/order, etc.) numbers with unique machine
> numbers embedded in them (as the left-most 3-4 digits, for example) so
> even if the transactions are fed into a network-disconnected computer
> the transaction numbers stay unique because of the left-most
> machine-number digits in the transaction-numbers. However, the manual
> system has a separate document number series for each department - so
> even if I store manual document numbers in -ve and use the left most
> 3-4 digits of the transaction-number column as the embedded
> machine-numbers (to make the transaction number unique, that is - in
> case connection to the server is dropped), I am going to have
> duplication errors - unless I get the department-no in the unique
> index (can't use a primary key as I am using InnoDB).
>
> I am storing all types of transactions in a single master/detail table
> combination with each transaction distinguished by its transaction
> type (order, invoice, purchase requisition, cash sale, etc.) However,
> that puts one more field into the index - in addition to increasing
> the data load on the tables. I decided on a single two-table design
> because the total number of transactions per year is not very big -
> last year the total number of transaction was under 100,000 - i.e.
> under hundred thousand. I reckon that it can go as high as 500,000 to
> 1000,000 but not much in the near future.
>
> If I create separate tables for each transaction type - invoice,
> order, cash sale, credit sale, etc. then I fear the system will be
> having to deal with too many tables (there are at least 10 transaction
> types). Since keeping different types of transactions in different
> tables will only decrease the index key length by 1 and there will
> still be three-columns in the indexs and there will be "many" tables
> with three-column indexes, do you guys think that splitting up the
> tables like this will increase performance?
>
> Lastly, my database is fully normalized and I have tried to enformce
> data-integrity at the database level with all constraints enforced.
> Since, on innoDB tables, there is a requirement of building indexes
> for foreign key constraints, I fear I'll have performance degradation
> problems on multi-column indexes. Because of that I have designed the
> database so that there will be very few updates or deletes - because
> of the stuff that I read about InnoDB issues. Does MySQL performs
> well with so many constraints enforced? I have STRICT SQL and all
> other restricted clauses enabled in my.ini file.
>
> Cutting it short: can you recommend a solution that I can use to
> reduce the number of columns in indexes? can you give me an advice to
> increase the MySQL performance in the face of fully-enforced
> constraints?
>
> I am posting this query on this list because I have seen some very
> good responses to similar problems on this list.
>
> Thanks in advance,
>
> Asif
>