> >> > > >maybe this is a silly question but how useful it is to create > >> > > >indexes > >> on > >> > >>columns containing only values 0 and 1 (true and false)? > >> > > > >> > > Perhaps, instead of the index, you might revise your schema a bit. > >> > > > >> > > Why do you have this boolean column? What are you trying to > >> > > achieve? > >> > > > >> > > >> > > >> > I use boolean columns as a flags mostly to mark records as 'exported'. > > For > >> > example I have table 'Transactions' and I have to export data for some > > of > >> > our customers (never for all of them). > >> > >> Right. Flags. IMO, flags are bad. > >> > >> And, as you noticed, it will slow down any queries because a flag cannot > >> be indexed properly. > >> > >> Instead, why create a table > >> Transactions_Exported > >> TransactionID <primary key> > >> > >> This can be indexed and will be fast if you want to get info about > >> any exported transactions. > > do you think that to join tables 'AllTrans' and 'ExportedTrans' and look for > 'ExportedTrans'.'TransID' IS NULL is better and quicker way than using a > flag column 'Exported'? Table 'AllTrans' will keep hundreds of thousands > transactions and approximately half of them will be exported.
To look for non-exported transactions, this might not be the best way :-) Perhaps a: Where TransID not in (select TransID from ExportedTrans) would be better... > > PS: if "being exported" is the final stage in the process, you might want > > to consider "being not exported" as the triggering condition and store > > that instead. That way, you can always quickly find the not-yet exported > > transactions. > > > I have to keep all transactions in one table for our reporting and futher > analysing and if some other customers need exports I just can create batch > sripts which will export their data for their own processing. > > Thank you for your help and time and also thanks others which responded to > my question. The best thing you can do is creating a bunch of test data. Use the flag. Test it. Next, try other ways. Test it. Measuring is knowing. And take a look at the query plans involved. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
