From: Peter Brawley Date: September 21 2006 3:24pm Subject: Re: Question about LOTS of indexes on a table List-Archive: http://lists.mysql.com/mysql/201994 Message-Id: <4512AE9C.30607@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Peter I am not sure what OLTP/OLAP means? T=transaction (processing is mostly inserts, updates), A=Analysis(processing is mostly for reports), see http://wiki/en/wikipedia.org/OLAP. You keep 2 versions of your data, one optimised for inserts/updates, one optimised for reporting, you update the latter from the former as often as your report consumers need up-to-dateness, and you route queries to the appropriate db. The differentiation can be whole-hog (2 dbs), or it can be partial, ie a few denormalised report-optimised tables in one db. PB ----- Peter Van Dijck wrote: > Thanks for the tips. So it seems that: > > 1) I should index the most often used ones. > > I am not sure what OLTP/OLAP means? > > Peter > > On 9/20/06, Peter Brawley wrote: >> >> Peter >> >> >It doesn't seem like it would make sense to make an index for every >> >possible combination... but there must be a way to do this >> >intelligently? >> >> It does not make sense for inserts and updates, but it sure makes >> sense for >> reproting, so have you considered separating your functionality into >> OLTP >> and OLAP dbs? >> >> PB >> >> ----- >> >> Peter Van Dijck wrote: >> Hi, >> I've been trying to figure this out for a while.. >> >> I have a table ITEMS with about 15 fields that can be used in any >> combination in where queries, let me call these fields f1 to f15. >> There are also 3 fields used for ordering, let's call them o1 to o3. >> >> So the table is: >> tablename (id, title, f1, f2, f3, f4, ..., f15, o1, o2, o3) >> >> f1 to f15 are all int(11). The table contains, let's say, 50,000 rows. >> >> The queries sent against this table can combine up to 4 f-fields, and >> 1 ordering field. >> >> So it could be: >> - SELECT * FROM table WHERE f3=x AND f7=x AND f12=x ORDER BY o3 DESC >> and so on.. you get the idea. >> >> The question is: we need indexes to make this go fast. How many >> indexes do we need? >> >> It doesn't seem like it would make sense to make an index for every >> possible combination... but there must be a way to do this >> intelligently? >> >> Any input is very welcome! I hope I explained the problem clearly? >> >> Thanks, >> Peter >> >> >> No virus found in this outgoing message. >> Checked by AVG Free Edition. >> Version: 7.1.405 / Virus Database: 268.12.5/451 - Release Date: >> 9/19/2006 >> >> >> > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.12.6/453 - Release Date: 9/20/2006