I am regularly using indices on medium-big tables (1000 to > 5
entries), and even on temporary tables (which I use a lot) in joins
(EXPLAIN SELECT is your friend).
But I'd never thought indices were needed for small tables (100-200
entries). I recently found they are useful too,
te on index on
> LONG VALUE, the index on LONG VALUE will actually work as a typical
> compound index on ID,VALUE. My question is this, if I don't know that
No, it will (sort of) work as a compound index on (VALUE, ID).
> about MySQL and create my indices on purpose (i.e. not t
Hi !
Robert DiFalco wrote:
Right, the proof is that if I have an PKEY on ID and an index just on
VALUE in MySQL then a query that would use both ID and VALUE works fine
with just the index on VALUE.
An index is a means to speed up access to the data when the most
identifying thing, the primar
blurry when the PKEY is compound.
-Original Message-
From: Olexandr Melnyk [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2008 11:08 AM
To: mysql@lists.mysql.com
Subject: Indices in InnoDB/MySQL
On 4/1/08, Paul DuBois <[EMAIL PROTECTED]> wrote:
>
> At 10:01 AM -0700 4/
There is already a primary key index on ID.
-Original Message-
From: Wm Mussatto [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2008 10:50 AM
To: mysql@lists.mysql.com
Subject: Re: Indices in InnoDB/MySQL
On Tue, April 1, 2008 10:01, Robert DiFalco wrote:
> I've been told
On 4/1/08, Paul DuBois <[EMAIL PROTECTED]> wrote:
>
> At 10:01 AM -0700 4/1/08, Robert DiFalco wrote:
> >I've been told that an index always contains the primary key.
>
>
> By who?
>
> Ask for proof.
I guess he was referring to the fact that InnoDB stores the primary key
values alongside the ind
pound index on ID,VALUE. My question is this, if I don't know that
about MySQL and create my indices on purpose (i.e. not taking into
account implementation details about MySQL) I would create the index
explicitly on ID,VALUE. If I did that would I effectively have the same
index as one on jus
x on ID,VALUE. My question is this, if I don't know that
> about MySQL and create my indices on purpose (i.e. not taking into
> account implementation details about MySQL) I would create the index
> explicitly on ID,VALUE. If I did that would I effectively have the same
> index a
MySQL and create my indices on purpose (i.e. not taking into
account implementation details about MySQL) I would create the index
explicitly on ID,VALUE. If I did that would I effectively have the same
index as one on just VALUE or would there some how be duplicate data in
the ID,VALUE index (i.e. ID,
Hi,
Try to make a table for each company with only one integer field than
insert a record and use the id to populate the other table.
You can also delete periodically records from there companies tables.
Santino
At 21:48 -0500 23-11-2007, David T. Ashley wrote:
Hi,
I am developing a large dat
Hi,
I am developing a large database where the web interface may be shared
among many companies, but the data will generally not be shared. For
the purposes of example, let's call it a bug tracking system such as
Bugzilla. Each company has their own private software bugs.
Many companies may ent
Gerald L. Clark wrote:
James Tu wrote:
I was a little to quick with the send button.
Can you do a query like this:
(I know that the * syntax is not correct, but is there something
equivalent to it?
SELECT from cars
WHERE
make=5 AND
model=* AND
body_color=7 AND
tire_type = *
James Tu wrote:
I was a little to quick with the send button.
Can you do a query like this:
(I know that the * syntax is not correct, but is there something
equivalent to it?
SELECT from cars
WHERE
make=5 AND
model=* AND
body_color=7 AND
tire_type = * AND
hub_caps_type =
one of the indices is used.
What is the proper way to setup indices in this case?
Shoud I add an Index for each of these fields OR create a
multicolumn
index using all of these fields?
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscrib
Hi James, all,
James Tu wrote:
What do you guys think of this approach...
Always query on all 5 columns...and then create a multicolumn index
using all 5 columns?
From a database point of view, that would be best -
but I am not sure whether it fits your application and the users.
Some poi
just
model = 22
I read that MySQL only uses one index when it performs a query. I
did an EXPLAIN and it appears that only one of the indices is used.
What is the proper way to setup indices in this case?
Shoud I add an Index for each of these fields OR create a
multicolumn
index using all of th
any of the
fields as criteria.
For example someone might search for :
body_color = 1 AND tire_type = 11
or just
model = 22
I read that MySQL only uses one index when it performs a query. I
did an EXPLAIN and it appears that only one of the indices is used.
What is the proper way to setup indices in
model = 22
I read that MySQL only uses one index when it performs a query. I
did an EXPLAIN and it appears that only one of the indices is used.
What is the proper way to setup indices in this case?
Shoud I add an Index for each of these fields OR create a multicolumn
index using all of these fields
search for :
body_color = 1 AND tire_type = 11
or just
model = 22
I read that MySQL only uses one index when it performs a query. I
did an EXPLAIN and it appears that only one of the indices is used.
What is the proper way to setup indices in this case?
Shoud I add an Index for each of these
Horst Jäger ha scritto:
Hi everyone,
the number of incices per table seems to be restricted to 64.
Any way to change that?
recompile with "configure --with-max-indexes=128"
I'm using MySQL 5.0.27 .
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscrib
Hi everyone,
the number of incices per table seems to be restricted to 64.
Any way to change that?
I'm using MySQL 5.0.27 .
Thanks in advance
Horst
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I have some long VARCHAR fields that a user will sometimes sort on. Does
a prefix index in any way help with sorting or just for lookups? Will it
speed up a filesort? I couldn't find this information in "How MySQL uses
indices".
R.
--
MySQL General Mailing List
For list
-
From: ""Robert DiFalco"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 09, 2006 8:41 PM
Subject: RE: InnoDB Indices
=20
++ I can't see it helping with insert, but depending on the where
clause on your updates and deletes it could.
make deletes faster. But every
database engine handles this stuff differently.
R
-Original Message-
From: David Turner [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 10:13 AM
To: Robert DiFalco; mysql@lists.mysql.com
Subject: Re: InnoDB Indices
- Original Message
- Original Message
From: Robert DiFalco <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Thursday, March 9, 2006 9:32:44 AM
Subject: InnoDB Indices
I have some questions regarding InnoDB indices.
Say I have a table with millions of records. On of the fields is a type
fiel
"Robert DiFalco" <[EMAIL PROTECTED]> wrote on 03/09/2006 12:32:44 PM:
> I have some questions regarding InnoDB indices.
>
> Say I have a table with millions of records. On of the fields is a type
> field that has a possible value of 1,2,3, or 4. I sometimes query by
I have some questions regarding InnoDB indices.
Say I have a table with millions of records. On of the fields is a type
field that has a possible value of 1,2,3, or 4. I sometimes query by the
type field and may at other times order on it.
Do queries benefit from an index with this low of a
now what it is now, though,
and have (or already have) considered adding support for it in MySQL.
-Hank
On 10/5/05, C.R. Vegelin <[EMAIL PROTECTED]> wrote:
> Hi Hank,
> You are quite right.
> I need separate non-unique indices on a, b, c, d, e and f to avoid table
> scans.
> An
Hi Hank,
You are quite right.
I need separate non-unique indices on a, b, c, d, e and f to avoid table
scans.
And when each combi must be unique I need a Primary Key (a,b,c,d,e,f).
And only Key a (a) seems to be redundant with the primary key ...
Suppose there would be a PK (a,b,c,d,e,f
It depends.. if this is your create table statement:
CREATE TABLE foo (
a smallint NOT NULL,
b smallint NOT NULL,
c smallint NOT NULL,
d smallint NOT NULL,
e smallint NOT NULL,
f smallint NOT NULL,
PRIMARY KEY (a,b,c,d,e,f)
);
Then only one unique index is being created on the conc
insert would have to include a full table scan.
Alec
"C.R. Vegelin" <[EMAIL PROTECTED]>
04/10/2005 15:10
To
cc
Subject
Re: How to avoid redundancy between PK and indices ?
Hi Alec,
Thanks for your comment. Well, we disagree on a few points.
Suppose I h
s etc.
So I need 4 separate indices, where CountryId may occur more than once in
the CountryId index,
CompanyID may occur more than once in the CompanyID index etc.
But if these 4 columns together are defined as Primary Key, then each combi
of CountryID, CompanyID, SectorID and ProductID is uniqu
"C.R. Vegelin" <[EMAIL PROTECTED]> wrote on 04/10/2005 12:52:01:
> Hi List,
>
> Is anyone familiar with optimizing indices, including primary key ?
> I do have a large myisam table with 6 non-unique key fields, lets
> say named A, B, C, D, E and F.
> Each
Hi List,
Is anyone familiar with optimizing indices, including primary key ?
I do have a large myisam table with 6 non-unique key fields, lets say named A,
B, C, D, E and F.
Each of these columns may have NOT NULL values from 0 to 999, and are defined
as SmallInt.
Requirement: each row must
L PROTECTED]> wrote:
> Greetings,
>I am trying to get a feel of how MySQL would handle certain types of
> situation, mainly concerning the usage of indices.
>
>Say I have two exactly identical table structures namely table A & B.
>
>For table A, I just
Manoj <[EMAIL PROTECTED]> wrote on 07/14/2005 06:09:24 AM:
> Greetings,
> I am trying to get a feel of how MySQL would handle certain types of
> situation, mainly concerning the usage of indices.
>
> Say I have two exactly identical table structures namely table
Greetings,
I am trying to get a feel of how MySQL would handle certain types of
situation, mainly concerning the usage of indices.
Say I have two exactly identical table structures namely table A & B.
For table A, I just have one composite primary key on (Code, Date1 &am
Greetings,
I am trying to get a feel of how MySQL would handle certain types of
situation, mainly concerning the usage of indices.
Say I have two exactly identical table structures namely table A & B.
For table A, I just have one composite primary key on (Code, Date1 &am
ank you. But I want select the constraints and indices used on
the
> > table. How can we get this information?. Please help me in this.
>
> Ian gave you the answer:
>
> > From: Ian Sales
> [...]
> > - show indexes from DATABASE_NAME.TABLE_NAME
>
> The synt
[EMAIL PROTECTED] wrote:
Hi,
Thank you. But I want select the constraints and indices used on the
table. How can we get this information?. Please help me in this.
Ian gave you the answer:
From: Ian Sales
[...]
- show indexes from DATABASE_NAME.TABLE_NAME
The syntax is: SHOW INDEX FROM
Hi,
Thank you. But I want select the constraints and indices used on the
table. How can we get this information?. Please help me in this.
Thanks,
Narasimha
-Original Message-
From: Ian Sales [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 02, 2004 5:07 PM
To: Lakshmi NarasimhaRao
[EMAIL PROTECTED] wrote:
Need to know the similar one in MySQL. Do we have views or any other
system tables in MySQL 4.0.21 which OUTPUTS the constraints and indices
in a particular table?
- show indexes from DATABASE_NAME.TABLE_NAME
- or, show create table DATABASE_NAME.TABLE_NAME;
- ian
Thats usually setup in the same table schema no ?
On 02/12/2004, at 9:52 PM, <[EMAIL PROTECTED]> wrote:
Hi All,
In oracle I can query user_constraints, user_indexes for getting the
constraints and indices for a particular table.
User_constraints and user_idexes are view which holds a
Hi All,
In oracle I can query user_constraints, user_indexes for getting the
constraints and indices for a particular table.
User_constraints and user_idexes are view which holds all the
constraints and indices for a particular table.
Need to know the similar one in MySQL. Do we have views
First, full text indices have nothing to do with importing the data,
it's just a special type of indexing for searching. The reason you got
several rows is that you probably did not specify delimiters. Thus,
every time a return was encountered, a new record was created because
return i
Brandon Carter wrote:
Is it possible to fit an entire article (say, a newspaper article) into one cell of a MySQL database? When I tried load data local infile the file was imported into several rows! Perhaps I just don't understand the use of a fulltext index.
--bhcesl
Do you Yahoo!?
Yahoo! F
Is it possible to fit an entire article (say, a newspaper article) into one cell of a
MySQL database? When I tried load data local infile the file was imported into
several rows! Perhaps I just don't understand the use of a fulltext index.
--bhcesl
Do you Yahoo!?
Yahoo! Finance Tax Center -
Hi
can anybody tell me the most common reason WHY an index file get's
corrupted?
I have tested this on differernt versions of MySQL (3.23.37 - 4.1) on Linux
and on Windows.
It happens on tables containing
A MEDIUMTEXT or LONGTEXT column,
A fulltext index on these columns
I am mostly work
On Tuesday 18 March 2003 02:47, dreq jkj wrote:
> I have trouble figuring out how the indices are used when making a query
> that uses OR-operator.
>
> If I have the following table:
>
> create table testing(
> id int unsigned not null primary key auto_increment,
> id
I have trouble figuring out how the indices are used when making a query
that uses OR-operator.
If I have the following table:
create table testing(
id int unsigned not null primary key auto_increment,
idx1 int unsigned not null,
idx2 int unsigned not null,
index(idx1),
index(idx2));
In this
l1,email2 | NULL |NULL | NULL | 57051 | Using
>
> where |
> ++--+---+--+-+--+---+--
>---+
[skip]
> I'm curious why this is. (In general, I have been surprised by how often
> indices are not used even if they exist.)
it's know behaviour and descr
--+--+-+
| person | ref | email1,email2 | email1 | 101 | const |1 |
Using where |
++--+---++-+---+--+-+
I'm curious why this is. (In general, I have been surprised by how often
indices are no
>>
>>Very nice to know that adding an index forces a rebuild of all indices!
>>(Side note -- I was going to configure the database with a minimal set
>>of indices, and then watch to see how people use the database, and then
>>add indices on popular columns.)
>>
On Mon, Sep 23, 2002 at 07:25:17AM -0500, Chris Stoughton wrote:
> Joseph,
>
> Thanks for the quick answer.
>
> Very nice to know that adding an index forces a rebuild of all indices!
> (Side note -- I was going to configure the database with a minimal set
> of indices,
Joseph,
Thanks for the quick answer.
Very nice to know that adding an index forces a rebuild of all indices!
(Side note -- I was going to configure the database with a minimal set
of indices, and then watch to see how people use the database, and then
add indices on popular columns.)
I did
is a "small" prototype for
> the actual database, which will have 40 times more rows.
>
> I used two strategies to create these tables:
>
> 1. Create the indices in the "create table" statement
> 2. Load the table, and then use the "create index&
the actual database, which will have 40 times more rows.
I used two strategies to create these tables:
1. Create the indices in the "create table" statement
2. Load the table, and then use the "create index" statement for each
index.
With Strategy 1, the loading started nicely
times when creating the
index. For example:
create fulltext index test
on TxKeywords
(Title,Title,Description);
seems to have roughly the desired effect, but I don't exactly
understand what it's doing, and I don't know if this is supported
behavior, or if might go
Hello Heikki,
Wednesday, November 21, 2001, 8:34:13 PM, you wrote:
I see some more users are interested in optimize table to work.
Why don't you just map OPTIMIZE TABLE to ALTER TABLE for INNODB tables
so user may not care about table type, there currently one should
check the table type and use
Hi!
>On Wed, 2001-11-21 at 05:19, Heikki Tuuri wrote:
>> The way to defragment InnoDB tables, or tables in any database, is from time
>> to time to dump and reimport them. That can give a significant performance
>> boost.
>>
>
>That is actually not entirely true. For MyISAM tables, one simply n
On Wed, 2001-11-21 at 05:19, Heikki Tuuri wrote:
> The way to defragment InnoDB tables, or tables in any database, is from time
> to time to dump and reimport them. That can give a significant performance
> boost.
>
That is actually not entirely true. For MyISAM tables, one simply needs
to run
and reimport them. That can give a significant performance
boost.
Regards,
Heikki
-Original Message-
From: nsabbi <[EMAIL PROTECTED]>
To: Heikki Tuuri <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Wednesday, November 21, 2001 11:37 AM
Subject: inno
HI,
I have two problems:
1)
I have the following table:
desc users;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| username | varchar(128) | | PRI |
On Tue, 21 Aug 2001 18:55:15 +0400
Spirit <[EMAIL PROTECTED]> wrote:
> Hello!
>
> I need your suggestions on what keys to build for the table described
> below. Currently I have almost all possible keys on the table in order
> to see what keys mysql will use.
>
> I have a table here, which cons
Hello!
I need your suggestions on what keys to build for the table described
below. Currently I have almost all possible keys on the table in order
to see what keys mysql will use.
I have a table here, which consist of over 6 million records by now
and is defined as follows:
CREATE TABLE traffi
Hi List,
I've a question about inserting values in tables with promary-keys.
I have two tables:
1. Table ( contains limits for testing )
- int limitId primary key
- int lowerlimit
- int upperlimit
- char(5) measureunit
- char(10) measurename
2. Table ( contains measured values )
- int measureid
hello mysql;
how can I recreate indices from .frm and .MYD?
laplace:/mnt/bigboy/mysql/newage# /usr/local/mysql/bin/myisamchk -rv
USR_User.MYD
/usr/local/mysql/bin/myisamchk: error: 'USR_User.MYD' doesn't have a
correct index definition. You need to recreate it before you can d
Hi,
you may know the feature
create table xyz select * from xy...
which is similar to oracles select into table.
It works ok, only thing is that with this statement no indices are
created on the new table. Is this a bug?
Have a nice thread,
Peter
68 matches
Mail list logo
