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