From: Peter Brawley
Date: September 21 2006 3:28am
Subject: Re: Question about LOTS of indexes on a table
List-Archive: http://lists.mysql.com/mysql/201963
Message-Id: <451206CA.5010005@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-451206CA317D======="
--=======AVGMAIL-451206CA317D=======
Content-Type: multipart/alternative; boundary=------------080101010204000509060004
--------------080101010204000509060004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
--------------080101010204000509060004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
--------------080101010204000509060004--
--=======AVGMAIL-451206CA317D=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
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
--=======AVGMAIL-451206CA317D=======--