hi list,

reading manual on mysql regarding auto_increment with multiple-column
index:

CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);

INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale '),
('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+

my question is what id would be if i:

UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
`name`='ostrich' LIMIT 1;

i'm confused on auto_increment now.

thanks

Sebastian Mendel schrieb:
> Hiep Nguyen schrieb:
>> hi list,
>>
>> reading manual on mysql regarding auto_increment with multiple-column
>> index:
>>
>> CREATE TABLE animals (
>> grp ENUM('fish','mammal','bird') NOT NULL,
>> id MEDIUMINT NOT NULL AUTO_INCREMENT,
>> name CHAR(30) NOT NULL,
>> PRIMARY KEY (grp,id)
>> );
>>
>> INSERT INTO animals (grp,name) VALUES
>> ('mammal','dog'),('mammal','cat'),
>> ('bird','penguin'),('fish','lax'),('mammal','whale '),
>> ('bird','ostrich');
>>
>> SELECT * FROM animals ORDER BY grp,id;
>>
>> +--------+----+---------+
>> | grp | id | name |
>> +--------+----+---------+
>> | fish | 1 | lax |
>> | mammal | 1 | dog |
>> | mammal | 2 | cat |
>> | mammal | 3 | whale |
>> | bird | 1 | penguin |
>> | bird | 2 | ostrich |
>> +--------+----+---------+
>>
>> my question is what id would be if i:
>>
>> UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2'
>> AND `name`='ostrich' LIMIT 1;
>
> you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
>
> your key is grp,id (bird,2)
>
> but your query will fail, because there is already grp,id (mammal,2) and
> therre can not be two identical UNIQUE (PRIMARY) keys

auto_increment comes only in effect when inserting NULL (or 0 in some SQL
mode) or nothing (with default NULL, 0 what should be always the case for
auto_increment fields)

your query should look like this:

UPDATE `animals`
SET `grp` = 'mammal',
`id` = NULL
WHERE `grp` = 'bird'
AND `id` = '2'
LIMIT 1;

--
Sebastian Mendel

Are you sure, I just get:

CREATE TABLE ...

ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key

On version 5.0.41. What version are you using?



Hiep Nguyen wrote:
> hi list,
>
> reading manual on mysql regarding auto_increment with multiple-column
> index:
>
> CREATE TABLE animals (
> grp ENUM('fish','mammal','bird') NOT NULL,
> id MEDIUMINT NOT NULL AUTO_INCREMENT,
> name CHAR(30) NOT NULL,
> PRIMARY KEY (grp,id)
> );
>
> INSERT INTO animals (grp,name) VALUES
> ('mammal','dog'),('mammal','cat'),
> ('bird','penguin'),('fish','lax'),('mammal','whale '),
> ('bird','ostrich');
>
> SELECT * FROM animals ORDER BY grp,id;
>
> +--------+----+---------+
> | grp | id | name |
> +--------+----+---------+
> | fish | 1 | lax |
> | mammal | 1 | dog |
> | mammal | 2 | cat |
> | mammal | 3 | whale |
> | bird | 1 | penguin |
> | bird | 2 | ostrich |
> +--------+----+---------+
>
> my question is what id would be if i:
>
> UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
> `name`='ostrich' LIMIT 1;
>
> i'm confused on auto_increment now.
>
> thanks
>

Hiep Nguyen schrieb:
> hi list,
>
> reading manual on mysql regarding auto_increment with multiple-column
> index:
>
> CREATE TABLE animals (
> grp ENUM('fish','mammal','bird') NOT NULL,
> id MEDIUMINT NOT NULL AUTO_INCREMENT,
> name CHAR(30) NOT NULL,
> PRIMARY KEY (grp,id)
> );
>
> INSERT INTO animals (grp,name) VALUES
> ('mammal','dog'),('mammal','cat'),
> ('bird','penguin'),('fish','lax'),('mammal','whale '),
> ('bird','ostrich');
>
> SELECT * FROM animals ORDER BY grp,id;
>
> +--------+----+---------+
> | grp | id | name |
> +--------+----+---------+
> | fish | 1 | lax |
> | mammal | 1 | dog |
> | mammal | 2 | cat |
> | mammal | 3 | whale |
> | bird | 1 | penguin |
> | bird | 2 | ostrich |
> +--------+----+---------+
>
> my question is what id would be if i:
>
> UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
> `name`='ostrich' LIMIT 1;

you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE

your key is grp,id (bird,2)

but your query will fail, because there is already grp,id (mammal,2) and
therre can not be two identical UNIQUE (PRIMARY) keys

--
Sebastian

Ben Clewett schrieb:
> Are you sure, I just get:
>
> CREATE TABLE ...
>
> ERROR 1075 (42000): Incorrect table definition; there can be only one
> auto column and it must be defined as a key

the mentioned CREATE TABLE is fine and works


> On version 5.0.41. What version are you using?

this works on all versions, and the example is from the MySQL manual

http://dev.mysql.com/doc/refman/5.1/...increment.html

--
Sebastian Mendel

You are right, I've tried 5.0.18 and 5.0.45 which work.

There must have been a bug in 5.0.41 with which I used test the question...

I belive the question has been answered by now anyway

Ben


Sebastian Mendel wrote:
> Ben Clewett schrieb:
>> Are you sure, I just get:
>>
>> CREATE TABLE ...
>>
>> ERROR 1075 (42000): Incorrect table definition; there can be only one
>> auto column and it must be defined as a key
>
> the mentioned CREATE TABLE is fine and works
>
>
>> On version 5.0.41. What version are you using?
>
> this works on all versions, and the example is from the MySQL manual
>
> http://dev.mysql.com/doc/refman/5.1/...increment.html
>

On Tue, 22 Apr 2008, Sebastian Mendel wrote:

> Sebastian Mendel schrieb:
>> Hiep Nguyen schrieb:
>>> hi list,
>>>
>>> reading manual on mysql regarding auto_increment with multiple-column
>>> index:
>>>
>>> CREATE TABLE animals (
>>> grp ENUM('fish','mammal','bird') NOT NULL,
>>> id MEDIUMINT NOT NULL AUTO_INCREMENT,
>>> name CHAR(30) NOT NULL,
>>> PRIMARY KEY (grp,id)
>>> );
>>>
>>> INSERT INTO animals (grp,name) VALUES
>>> ('mammal','dog'),('mammal','cat'),
>>> ('bird','penguin'),('fish','lax'),('mammal','whale '),
>>> ('bird','ostrich');
>>>
>>> SELECT * FROM animals ORDER BY grp,id;
>>>
>>> +--------+----+---------+
>>> | grp | id | name |
>>> +--------+----+---------+
>>> | fish | 1 | lax |
>>> | mammal | 1 | dog |
>>> | mammal | 2 | cat |
>>> | mammal | 3 | whale |
>>> | bird | 1 | penguin |
>>> | bird | 2 | ostrich |
>>> +--------+----+---------+
>>>
>>> my question is what id would be if i:
>>>
>>> UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND
>>> `name`='ostrich' LIMIT 1;
>>
>> you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
>>
>> your key is grp,id (bird,2)
>>
>> but your query will fail, because there is already grp,id (mammal,2) and
>> therre can not be two identical UNIQUE (PRIMARY) keys
>
> auto_increment comes only in effect when inserting NULL (or 0 in some SQL
> mode) or nothing (with default NULL, 0 what should be always the case for
> auto_increment fields)
>
> your query should look like this:
>
> UPDATE `animals`
> SET `grp` = 'mammal',
> `id` = NULL
> WHERE `grp` = 'bird'
> AND `id` = '2'
> LIMIT 1;
>
> --
> Sebastian Mendel
>

thanks, i got it.

Hi everybody,

I am back to this list after a long period away due to work time
restrictions... I have great news and a few interesting applications
that I will release to the mysql community very soon, most probably as
open source.

But now I have a performance problem with a client of mine, that I was
not able to solve... The problem is that I have a very large table in
terms of data, about 7.000.000 financial transactions records, with
the following table (translated from portuguese):

CREATE TABLE `transactions` (
`client_id` int(5) unsigned zerofill NOT NULL default '00000',
`client_unit_id` int(4) unsigned zerofill NOT NULL default '0000',
`client_property_id` int(6) unsigned zerofill NOT NULL default
'000000',
`transaction_id` int(6) unsigned zerofill NOT NULL default '000000',
`transaction_account_id` int(3) unsigned zerofill NOT NULL default
'000',
`transaction_classification_id` int(3) unsigned NOT NULL default '0',
`transaction_category_id` int(4) unsigned zerofill NOT NULL default
'0000',
`transaction_complement` varchar(200) NOT NULL,
`transaction_date` date default NULL,
`transaction_amount` decimal(16,2) NOT NULL,
`transaction_parcel` varchar(8) NOT NULL,
`transaction_nature` varchar(1) NOT NULL
KEY `transactions_idx_1`
(`
client_id
`,`client_unit_id`,`client_property_id`,`transacti on_account_id`,

`
transaction_classification_id
`
,`
transaction_category_id
`,`transaction_id`,`transaction_date`,`transaction _nature`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And most the queries are similar to this one:

SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date,
transactions.transaction_complement AS complement,
transactions.transaction_parcel AS parcel,
transactions.transaction_amount AS amount,
transactions.transaction_nature AS nature,
transactions_categories.transaction_category_descr iption AS
category_description
FROM transactions AS transactions
LEFT JOIN transactions_categories AS transactions_categories
ON transactions.transaction_category_id =
transactions_categories.transaction_category_id
WHERE transactions.client_id = :client
AND transactions.client_unit_id = :unit
AND transactions.transaction_date >= :start_date
AND transactions.transaction_date <= :stop_date
ORDER BY transactions.transaction_date,
transactions.transaction_id ASC

So the most important indexes are client_id , client_unit_id ,
client_property_id , transaction_account_id ,
transaction_classification_id , transaction_category_id ,
transaction_id , transaction_date , transaction_nature, and most of
the time they are called together, I thing the most problematic part
of those queries are the date range part, should I use a different
index only for this column to maintain the index small?

Most of the financials reports today takes about 8 to 12 seconds to be
generated for one month (course that I have to sum previous months
totals to give the balance).

Thanks in advance...

Regards,
Bruno B B Magalh'aes

I'm sure if you created an index on
client_id,client_unit_id,transaction_date (with optionally something else to
make unique) it would increase performance.

What does an EXPLAIN give you?

Phil

On Tue, Apr 22, 2008 at 11:41 AM, Bruno B. B. Magalhães <
brunomagalhaes@blackbean.com.br> wrote:

> Hi everybody,
>
> I am back to this list after a long period away due to work time
> restrictions... I have great news and a few interesting applications thatI
> will release to the mysql community very soon, most probably as open source.
>
> But now I have a performance problem with a client of mine, that I was not
> able to solve... The problem is that I have a very large table in terms of
> data, about 7.000.000 financial transactions records, with the following
> table (translated from portuguese):
>
> CREATE TABLE `transactions` (
> `client_id` int(5) unsigned zerofill NOT NULL default '00000',
> `client_unit_id` int(4) unsigned zerofill NOT NULL default '0000',
> `client_property_id` int(6) unsigned zerofill NOT NULL default '000000',
> `transaction_id` int(6) unsigned zerofill NOT NULL default '000000',
> `transaction_account_id` int(3) unsigned zerofill NOT NULL default '000',
> `transaction_classification_id` int(3) unsigned NOT NULL default '0',
> `transaction_category_id` int(4) unsigned zerofill NOT NULL default
> '0000',
> `transaction_complement` varchar(200) NOT NULL,
> `transaction_date` date default NULL,
> `transaction_amount` decimal(16,2) NOT NULL,
> `transaction_parcel` varchar(8) NOT NULL,
> `transaction_nature` varchar(1) NOT NULL
> KEY `transactions_idx_1`
> (`client_id`,`client_unit_id`,`client_property_id` ,`transaction_account_id`,
>
> `transaction_classification_id`,`transaction_categ ory_id`,`transaction_id`,`transaction_date`,`trans action_nature`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> And most the queries are similar to this one:
>
> SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date,
> transactions.transaction_complement AS complement,
> transactions.transaction_parcel AS parcel,
> transactions.transaction_amount AS amount,
> transactions.transaction_nature AS nature,
> transactions_categories.transaction_category_descr iption
> AS category_description
> FROM transactions AS transactions
> LEFT JOIN transactions_categories AS transactions_categories
> ON transactions.transaction_category_id =
> transactions_categories.transaction_category_id
> WHERE transactions.client_id = :client
> AND transactions.client_unit_id = :unit
> AND transactions.transaction_date >= :start_date
> AND transactions.transaction_date <= :stop_date
> ORDER BY transactions.transaction_date,
> transactions.transaction_id ASC
>
> So the most important indexes are client_id , client_unit_id ,
> client_property_id , transaction_account_id , transaction_classification_id
> , transaction_category_id , transaction_id , transaction_date ,
> transaction_nature, and most of the time they are called together, I thing
> the most problematic part of those queries are the date range part, should I
> use a different index only for this column to maintain the index small?
>
> Most of the financials reports today takes about 8 to 12 seconds to be
> generated for one month (course that I have to sum previous months totalsto
> give the balance).
>
> Thanks in advance...
>
> Regards,
> Bruno B B Magalh'aes
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=pchapman@nc.rr.com
>



--
Help build our city at http://free-dc.myminicity.com !

On Tue, Apr 22, 2008 at 8:41 AM, Bruno B. B. Magalhães <
brunomagalhaes@blackbean.com.br> wrote:

> Hi everybody,
>
> I am back to this list after a long period away due to work time
> restrictions... I have great news and a few interesting applications thatI
> will release to the mysql community very soon, most probably as open source.
>
> But now I have a performance problem with a client of mine, that I was not
> able to solve... The problem is that I have a very large table in terms of
> data, about 7.000.000 financial transactions records, with the following
> table (translated from portuguese):
>
> CREATE TABLE `transactions` (
> `client_id` int(5) unsigned zerofill NOT NULL default '00000',
> `client_unit_id` int(4) unsigned zerofill NOT NULL default '0000',
> `client_property_id` int(6) unsigned zerofill NOT NULL default '000000',
> `transaction_id` int(6) unsigned zerofill NOT NULL default '000000',
> `transaction_account_id` int(3) unsigned zerofill NOT NULL default '000',
> `transaction_classification_id` int(3) unsigned NOT NULL default '0',
> `transaction_category_id` int(4) unsigned zerofill NOT NULL default
> '0000',
> `transaction_complement` varchar(200) NOT NULL,
> `transaction_date` date default NULL,
> `transaction_amount` decimal(16,2) NOT NULL,
> `transaction_parcel` varchar(8) NOT NULL,
> `transaction_nature` varchar(1) NOT NULL
> KEY `transactions_idx_1`
> (`client_id`,`client_unit_id`,`client_property_id` ,`transaction_account_id`,
>
> `transaction_classification_id`,`transaction_categ ory_id`,`transaction_id`,`transaction_date`,`trans action_nature`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> And most the queries are similar to this one:
>
> SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date,
> transactions.transaction_complement AS complement,
> transactions.transaction_parcel AS parcel,
> transactions.transaction_amount AS amount,
> transactions.transaction_nature AS nature,
> transactions_categories.transaction_category_descr iption
> AS category_description
> FROM transactions AS transactions
> LEFT JOIN transactions_categories AS transactions_categories
> ON transactions.transaction_category_id =
> transactions_categories.transaction_category_id
> WHERE transactions.client_id = :client
> AND transactions.client_unit_id = :unit
> AND transactions.transaction_date >= :start_date
> AND transactions.transaction_date <= :stop_date
> ORDER BY transactions.transaction_date,
> transactions.transaction_id ASC
>
> So the most important indexes are client_id , client_unit_id ,
> client_property_id , transaction_account_id , transaction_classification_id
> , transaction_category_id , transaction_id , transaction_date ,
> transaction_nature, and most of the time they are called together, I thing
> the most problematic part of those queries are the date range part, should I
> use a different index only for this column to maintain the index small?
>
> Most of the financials reports today takes about 8 to 12 seconds to be
> generated for one month (course that I have to sum previous months totalsto
> give the balance).
>
> Thanks in advance...
>
> Regards,
> Bruno B B Magalh'aes
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=wultsch@gmail.com
>
> I would think probably not on the date range. An Phil says an EXPLAIN
would probably be helpful. Particularly of note would be the key_len. I
would image an optimal index would be something like
KEY `transactions_idx_1` (`client_id`,`client_unit_id`,`transaction_date`)

If memory serves MySQL does not use a composite index to the right of range
scan.


--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)