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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
