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
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 searches ?
On Fri, Oct 1, 2010 at 9:35 PM, Gavin
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
You can't use an index to select records in a range, and order them. The order
by will cause a filesort in that case.
Additionally indexes are always read left to right. So an index on ('user_id',
'product_id') will help when doing WHERE user_id=N AND product_id IN (1,2,3),
but wouldn't help
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
Ben
It would appear that the deletion of rows may be a problem.
After deleting rows older than 6 months, do you optimize the
table?
As I understand it, mysql does not delete delete, only marks
a bit for every row thats deleted. That way, delete speed is
fast. However, it slows down queries and
Since I been working implementing some triggers in my system
recently this suggestion first came into my mind:
create a delete trigger for the user table. Every time a user it deleted,
the trigger fires and could for example moves this information to a history
table, with date of deletion, etc.
Why not have the application check for existing usernames, if one
exists,present the info the admin user and ask if this is the user they
are trying to create, if yes, then change the active flag, if not, tell
them they need to choose a different username. That way your auditing
and your unique
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
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
22 matches
Mail list logo
