Thanks Gavin and Joerg, that was very helpful!
-- Jonas
On Sun, Oct 3, 2010 at 12:44 PM, Joerg Bruehe joerg.bru...@oracle.comwrote:
Hi Neil, all!
Tompkins Neil wrote:
So if you have individual indexes for example field_1, field_2 and
field_3
etc and then perform a search like
WHERE
Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Monday, October 04, 2010 8:54 AM
To: Joerg Bruehe
Cc: [MySQL]
Subject: Re: Indexing question
Jörg
Thanks for the useful reply. Maybe I can EXPLAIN my select queries for
you
to advise if any changes need to be made
Hi!
Neil Tompkins wrote:
Thanks for your reply. So should we create individual indexes on each
field or a multiple column index ??
This question cannot be answered without checking and measuring your
installation. The decision whether to create an index is always an act
of balancing:
- If
Jörg
Thanks for the useful reply. Maybe I can EXPLAIN my select queries for you
to advise if any changes need to be made ?
Regards
Neil
On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe joerg.bru...@oracle.comwrote:
Hi!
Neil Tompkins wrote:
Thanks for your reply. So should we create
: Re: Indexing question
Jörg
Thanks for the useful reply. Maybe I can EXPLAIN my select queries for you
to advise if any changes need to be made ?
Regards
Neil
On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe joerg.bru...@oracle.comwrote:
Hi!
Neil Tompkins wrote:
Thanks for your reply. So
Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Monday, October 04, 2010 8:54 AM
To: Joerg Bruehe
Cc: [MySQL]
Subject: Re: Indexing question
Jörg
Thanks for the useful reply. Maybe I can EXPLAIN my select queries
for you
to advise if any changes need to be made
11:48 AM
To: mysql@lists.mysql.com
Subject: Indexing question
Suppose I wanted to be able to perform queries against three columns of my
table: 'user_id', 'product_id' and 'created'. Most of the time I'll just be
range-selecting records from the table ordering by 'created'. But I may
also
Hi Neil, all!
Tompkins Neil wrote:
So if you have individual indexes for example field_1, field_2 and field_3
etc and then perform a search like
WHERE field_1 = 10
AND field_3 = 'abc'
This wouldn't improve the search ? You have to create a index for all
possible combined field
Thanks for your reply. So should we create individual indexes on each
field or a multiple column index ??
On 3 Oct 2010, at 16:44, Joerg Bruehe joerg.bru...@oracle.com wrote:
Hi Neil, all!
Tompkins Neil wrote:
So if you have individual indexes for example field_1, field_2 and
field_3
Following on from my previous email I have columns containing numbers
which are then used in SUM and MIN/ MAX functions should these be
indexed too ?
On 3 Oct 2010, at 16:44, Joerg Bruehe joerg.bru...@oracle.com wrote:
Hi Neil, all!
Tompkins Neil wrote:
So if you have individual indexes
Suppose I wanted to be able to perform queries against three columns of my
table: 'user_id', 'product_id' and 'created'. Most of the time I'll just be
range-selecting records from the table ordering by 'created'. But I may also
want to select where 'user_id' = something and 'product_id' in (list,
@lists.mysql.com
Subject: Indexing question
Suppose I wanted to be able to perform queries against three columns of my
table: 'user_id', 'product_id' and 'created'. Most of the time I'll just be
range-selecting records from the table ordering by 'created'. But I may also
want to select where 'user_id' = something
Hi,
I have created a rather large table containing about 16M records. Most
of the indexed fields are smallint, but there is one field that is a
text field that I am using fulltext indexing on. The total size of the
smallint indexes is only about 30 MB, but the fulltext index brings
the total index
What is the size of the text field you're fulltext indexing? How often is
that index used? You might be best off to create a table containing only
that column and a PK that is equal to the PK in the original table. You
might also keep a portion of the text field (say 50 characters) in the
original
The field has up to 1000 characters, usually well over 255. It is
searched constantly (2-3 times every second). The smallint values are
scanned up to 40 times a second. So, are you saying that the entire
MYI file is dumped into the keycache? If so, your suggestion to create
a separate table with
Hi all,
I would like to create an index to speed up the following query:
SELECT Account, Status, count(*) From MessageStatus WHERE sentDate
'(variable)' AND sentDate '(variable)' GROUP BY Account,Status ORDER BY
Account
sentDate is a timestamp(14), Account and Status are both varchars.
The
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 27, 2002 1:20 AM
To: Mysql z_mailing
Subject: Indexing question
Hi all,
I would like to create an index to speed up the following query:
SELECT Account, Status, count(*) From MessageStatus WHERE sentDate
'(variable)' AND sentDate
Hi all,
I have developed a system that lets people send messages to each other, with
a MySQL database and PHP.
Among the tables that exist, one is called Users and contains information
such as Username, password (md5'd of course), email address etc. This table
has a primary key UserID that is
. This table could then be scanned in case
no user was found.
-Original Message-
From: Ben Holness [mailto:[EMAIL PROTECTED]]
Sent: Sunday, May 26, 2002 9:48 PM
To: [EMAIL PROTECTED]
Subject: Indexing Question
Hi all,
I have developed a system that lets people send messages
indexing is preserved.
Andrew Hazen
-Original Message-
From: Ben Holness [mailto:[EMAIL PROTECTED]]
Sent: Sunday, May 26, 2002 3:48 PM
To: [EMAIL PROTECTED]
Subject: Indexing Question
Hi all,
I have developed a system that lets people send messages to each other,
with
a MySQL database
Hi All,
Let's say I'm creating an affiliate program to track surfer clicks to sales.
I have a question on indexing that's always bothered me. I'm hoping an index
guru can help me out. Here's an example click-recording table:
CREATE TABLE clicks (
clickID int unsigned NOT NULL auto_increment,
If you have an index consisting of member_id mail_id (opposite
order), you'll need just one index.
A good explanation of why may be found at:
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Multiple-column_indexes
-rh
I for sure need an index on the mail_id
I am going to have a table called mail_queue, that is going to store the
member id, the mailing id, and the date. Each time a new mailing is setup
(usually daily), another 100,000 rows (one for each member) will be added to
the table.
When a member reads the mailing, that row will be removed
Can anyone give me some tips on how indexes work?
I noticed that UNIQUE() seems to create a lock on all given fields per
call like UNIQUE (md5, mime) so that no row can have the same combination
of md5 and mime type which is good but assuming I wanted to have each
unique on it's own I'd need
See:
http://www.mysql.com/doc/C/R/CREATE_INDEX.html
http://www.mysql.com/doc/M/y/MySQL_indexes.html
Steve Meyers
-Original Message-
From: Michael [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 2:07 PM
To: [EMAIL PROTECTED]
Subject: indexing question
Can anyone
Thanks. I've read the manual. I guess I was looking for a more direct
explanation to make sure I had it clear and to learn any tips that might
be useful that wouldn't be in the manual. As my database will be quite
large I'm worried about effective optimizations.
Okay, then I'll go through it point by point :)
Thanks. I've read the manual. I guess I was looking for a more direct
explanation to make sure I had it clear and to learn any tips that might
be useful that wouldn't be in the manual. As my database will be quite
large I'm worried about
Okay, then I'll go through it point by point :)
Thanks. Helps a lot.
Yes they're the same. How you index depends on your queries.
Generally, just look at what your doing in your where clauses. If
you're looking up rows based just on the path, then index path. If
you're looking up rows
So there is no magic bullet that indexes everything so it works well with
any given query? Does it help to index each field by itself for general
queries and then I guess you index combinations of fields that will be
used together in a WHERE clause?
Nope, no magic bullet... Indexes speed
29 matches
Mail list logo
