Re: Different Count(distinct) in the same query
Even in the truncated version posted, the table has design issues that complicate retrieving info from it ...
- Capitalised column names are distracting to read; consider adopting the convention of using lower case to help along readability, support & maintenance
- numcliente bigint: That provides space for 18 quintillion (10^18) values, ie 2.5 billion values per human being on the planet! An ordinary unsigned int will track 4 billion customers, surely that's enough?
- bne_c_tx, bne_m_tx etc represent a textbook normalisation error: they're sibling columns, and the list of types they encode are likely to change, so they need to be moved ("projected") to a child table eg tx(id int primary key auto_increment, txtype char(1), numcliente int)
- customers do not take kindly to seeing rounding errors in their balances, so
double and float are exactly wrong for monetary values; use decimal.
- a given mysql query or subquery can usually use just one index for a given table, so
maintaining several one-column indexes is very likely a waste. Covering indexes should be designed for the queries they need to optimise, so will usually need to "cover" several columns.
Read about normalisation and about query optimisation, then redesign the table!
Edited 1 time(s). Last edit at 05/12/2014 10:18AM by Peter Brawley.