RE: Select Earliest Related Row

[Pavel Zimahorau]

Select class_name, 
GROUP_CONCAT(DISTINCT cl_date
  ORDER BY cl_date DESC SEPARATOR ',  ')
 (select min(cl_date) from CLASS_DATES where item_id = c.item_id and 
cl_date > Now())
From CLASSES c
Join CLASS_DATES cd on  (c.item_id = cd.item_id) 
Group by class_name, c. item_id

I did not check it in DB and it can have some parse errors. 

But It should work. 

Best Regards,
Pavel Zimahorau

-Original Message-
From: Don Wieland [mailto:d...@pointmade.net] 
Sent: Tuesday, February 09, 2016 6:57 PM
To: MySql 
Subject: Select Earliest Related Row

I have a two tables where I am adding CLASSES and CLASS_DATES for people to 
register for.

Table Name = tl_items (Parent)
item_id
class_name


table_name = tl_items_classes (Children)
class_date_id
item_id
cl_date

“tl_items" will have 0 to Many “tl_items_classes" using the “item_id” as the 
KEY field.

I am compiling a SELECT query to search the dates of the classes 
(tl_items_classes rows), but I need to these two things:

1) Only show the EARLIEST “cl_date" in my returned list (i.e... if a class has 
dates for Jan 2,3,4,5,6 2016, only show Jan 2’s row)

2) In that displayed EARLIEST Class Date row, have a column that displays the 
complete list of related class dates in ASC order delineated by a COMMA 
(Group_Concat()???).

Here is a query I have started off with which show all the dates fine. Just 
want to fine tune it.

SELECT ic.*, i.*, DATE_FORMAT(ic.cl_date, "%M %Y") AS mo_label FROM 
tl_items_classes ic 
LEFT JOIN tl_items i ON ic.item_id = i.item_id 
WHERE i.active = 1 AND ic.cl_cancelled IS NULL ORDER BY ic.cl_date ASC;

Any help would be appreciated.

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: select contiguous addresses that start on a bit boundary

[John Stile]

I should have said consecutive addresses, rather than contiguous.
I care about a set of consecutive addresses, and there is no guarantee
of record order.

On 12/17/2015 07:35 AM, John Stile wrote:
> I need help creating a select that returns 4 records that have
> contiguous addresses that start on a bit boundary.
>
> If 4 do not exist, I need a return of zero records.
>
> I would like to do this in one statement and I do not have ownership of
> this mysql server, so fancy views, temporary tables, indexing, etc are
> outside my permission level.
>
> I am also not the only consumer of this database, so altering it for my
> needs could hurt the other consumers.
>
> Below I specify the issue and where I am.
>
> Thank you for your attention.
>
> #
> # Create problem set
> # - This has non-contiguous addresses
> # - This has one status not 0
> # - This has contiguous addresses that start before the bit boundary
> #
> CREATE TABLE addresses ( address BIGINT(20), status INT );
> INSERT INTO addresses
> VALUES (1001,0),
>(1003,0),
>(1004,0),
>(1005,1),
>(1006,0),
>(1007,0),
>(1008,0),
>(1009,0),
>(1010,0),
>(1011,0),
>(1013,0),
>(1014,0),
>(1015,0),
>(1016,0),
>(1017,0);
> #
> # This shows the bit boundary, where the start is  (address & 3) = 0
> #
> select address, (address & 3) as boundary from addresses where address
>> 0 and status=0 order by address limit 10  ;
> +--+--+
> | address  | boundary |
> +--+--+
> | 1001 |1 |
> | 1003 |3 |
> | 1004 |0 |
> | 1006 |2 |
> | 1007 |3 |
> | 1008 |0 |
> | 1009 |1 |
> | 1010 |2 |
> | 1011 |3 |
> | 1013 |1 |
> +--+--+
> 10 rows in set (0.00 sec)
> #
> # This shows contiguous add, but they do not stat on the bit  boundary
> #
> select c1.address, (address & 3) as boundary  from addresses c1 where 4
> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
> BETWEEN c1.address AND (c1.address + 3)  ) limit 10;
>
> +--+--+
> | address  | boundary |
> +--+--+
> | 1006 |2 |
> | 1007 |3 |
> | 1008 |0 |
> | 1013 |1 |
> | 1014 |2 |
> +--+--+
> 5 rows in set (0.00 sec)
>
>
>
> I can't seem to add my ((address & 3) = 0) condition to the correct location 
> to get the desired
> result. I don't understand how I can use c1.address in the BETWEEN, and
> yet I can't seem to make ((address & 3) = 0) work anywhere.
>
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: select contiguous addresses that start on a bit boundary

[John Stile]

I have a solution.

SELECT start_bit_boundary FROM (
  SELECT
min(address) as start_bit_boundary,
status, count(*) as CT
  FROM MAC_addresses
  WHERE status = 0
  GROUP BY address >> 2
) AS _INNER
WHERE
  _INNER.CT = 4
ORDER BY start_bit_boundary
LIMIT 0,1;

It returns the first of 4 consecutive addresses.
This works with the following data set.

CREATE TABLE addresses ( address BIGINT(20), status INT );
INSERT INTO addresses
VALUES (1001,0),
   (1003,0),
   (1004,0),
   (1005,1),
   (1006,0),
   (1007,0),
   (1009,0),
   (1010,0),
   (1011,0),
   (1013,0),
   (1008,0),
   (1014,0),
   (1015,0),
   (1016,0),
   (1017,0);

If I want to print all the addresses I could do this:

select * from addresses
where status = 0
AND  address BETWEEN (
  SELECT @b := start_bit_boundary FROM (
SELECT min(address) as start_bit_boundary,status,count(*) as CT
FROM MAC_addresses
WHERE status = 0
GROUP BY address >> 2
  ) AS _INNER
  WHERE _INNER.CT = 4
  ORDER BY start_bit_boundary
  LIMIT 0,1
) AND (@b+3) limit 0,4;

On 12/17/2015 08:14 AM, John Stile wrote:
> I should have said consecutive addresses, rather than contiguous.
> I care about a set of consecutive addresses, and there is no guarantee
> of record order.
>
> On 12/17/2015 07:35 AM, John Stile wrote:
>> I need help creating a select that returns 4 records that have
>> contiguous addresses that start on a bit boundary.
>>
>> If 4 do not exist, I need a return of zero records.
>>
>> I would like to do this in one statement and I do not have ownership of
>> this mysql server, so fancy views, temporary tables, indexing, etc are
>> outside my permission level.
>>
>> I am also not the only consumer of this database, so altering it for my
>> needs could hurt the other consumers.
>>
>> Below I specify the issue and where I am.
>>
>> Thank you for your attention.
>>
>> #
>> # Create problem set
>> # - This has non-contiguous addresses
>> # - This has one status not 0
>> # - This has contiguous addresses that start before the bit boundary
>> #
>> CREATE TABLE addresses ( address BIGINT(20), status INT );
>> INSERT INTO addresses
>> VALUES (1001,0),
>>(1003,0),
>>(1004,0),
>>(1005,1),
>>(1006,0),
>>(1007,0),
>>(1008,0),
>>(1009,0),
>>(1010,0),
>>(1011,0),
>>(1013,0),
>>(1014,0),
>>(1015,0),
>>(1016,0),
>>(1017,0);
>> #
>> # This shows the bit boundary, where the start is  (address & 3) = 0
>> #
>> select address, (address & 3) as boundary from addresses where address
>>> 0 and status=0 order by address limit 10  ;
>> +--+--+
>> | address  | boundary |
>> +--+--+
>> | 1001 |1 |
>> | 1003 |3 |
>> | 1004 |0 |
>> | 1006 |2 |
>> | 1007 |3 |
>> | 1008 |0 |
>> | 1009 |1 |
>> | 1010 |2 |
>> | 1011 |3 |
>> | 1013 |1 |
>> +--+--+
>> 10 rows in set (0.00 sec)
>> #
>> # This shows contiguous add, but they do not stat on the bit  boundary
>> #
>> select c1.address, (address & 3) as boundary  from addresses c1 where 4
>> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
>> BETWEEN c1.address AND (c1.address + 3)  ) limit 10;
>>
>> +--+--+
>> | address  | boundary |
>> +--+--+
>> | 1006 |2 |
>> | 1007 |3 |
>> | 1008 |0 |
>> | 1013 |1 |
>> | 1014 |2 |
>> +--+--+
>> 5 rows in set (0.00 sec)
>>
>>
>>
>> I can't seem to add my ((address & 3) = 0) condition to the correct location 
>> to get the desired
>> result. I don't understand how I can use c1.address in the BETWEEN, and
>> yet I can't seem to make ((address & 3) = 0) work anywhere.
>>
>>
>>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Lucio Chiappetti]

On Wed, 29 Apr 2015, Olivier Nicole wrote:


I have a table where each record is made of one item_number and one
data_value.


You do not have any other column ? In particular you do not have any 
unique key record identifier ? All my tables have a column with a record 
sequence number seq int NOT NULL AUTO_INCREMENT which is also a key
KEY auxiliary(seq). This is useful a posteriori to locate particular 
records.



What is the command to select all the records where an item_number has
the data 1 but not the data 2?


1) by select you mean display at the terminal using the mysql line mode
   client, or locate all affected records for further work ?

2) am I getting it correctly that you want to locate all the cases where
   a given item_number (any) has JUST ONE occurrence in the table ?

In the line mode client this can be easily done with an additional table, 
which can be a temporary table.


Consider e.g. the following table (it has two columns, no seq column, and
nothing else ... actually it is a table of seq pointers in two other 
tables)


 select * from north33w1t7_ 

| north33 | w1t7 |
+-+--+
|  21 |1 |
|  21 |2 |
|  24 |   20 |

create temporary table temp1
select north33,count(*) as c from north33w1t7_
group by north33 order by north33;

temp1 will contain something like this

| north33 | c |
+-+---+
|  21 | 2 |
|  24 | 1 |

so it will tell you that item 21 has 2 counteparts, while item 24 
has 1 counterpart.


If you want to select (display) all cases in the main table with 1 
counterpart do


select north33w1t7_.*
from temp1 join north33w1t7_  on 
temp1.north33=north33w1t7_.north33

where c=1 :

| north33 | w1t7 |
+-+--+
|  24 |   20 |
|  200013 |   93 |


A different story would be if you want always to extract ONE record from 
the main table, the single one if c=1, and the FIRST one if c1.


What you define first it is up to you (the smallest data_value, the 
highest data_value, a condition on other columns).


Here in general I use a trick which involves one or two temporary tables 
and a variable.  I initialize the variable to zero (or a value which is 
not represented in the table, which shall be ordered on the columns as you 
need. Then I test whether the item_number is the same as the variable, if 
not I declare it to be first, then reset the variable in the same select 
statement.


 set @x:=0;
 select north33w1t7_.*,
 if(@xnorth33w1t7_.north33,'FIRST','no') as flag,
 @x:=north33w1t7_.north33
 from temp1 join north33w1t7_  on temp1.north33=north33w1t7_.north33
 where c1 order by north33,w1t

| north33 | w1t7 | flag  | @x:=north33w1t7_.north33 |
+-+--+---+--+
|  21 |1 | FIRST |   21 |
|  21 |2 | no|   21 |
|  22 |8 | FIRST |   22 |
|  22 |9 | no|   22 |

I can then save this select to a temporary table, and take my pick where 
flag='FIRST'.



of course you can also do without the join with temp1 if you want
either the single or the first (i.e. c=1 and c1)

select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from 
north33w1t7_  order by north33,w1t7


| north33 | w1t7 | flag  | @x:=north33 |
+-+--+---+-+
|  21 |1 | FIRST |  21 |
|  21 |2 | no|  21 |
|  22 |8 | FIRST |  22 |
|  22 |9 | no|  22 |
|  24 |   20 | FIRST |  24 |


--

Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html

Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Mogens Melander]

Right,

Take a look at this one then:

insert into test(item_number,data_value)
values(1,1),(1,2),(1,3)
,(2,1),(2,3)
,(3,1),(3,2),(3,3)
,(4,1),(4,3);

SELECT * FROM test
WHERE item_number in (SELECT item_number FROM test where data_value=1)
AND item_number not in (SELECT item_number FROM test where data_value = 2);

Result:
2, 1
2, 3
4, 1
4, 3

On Wed, April 29, 2015 07:20, Olivier Nicole wrote:
 SELECT * FROM table WHERE item_number=1;

 Sorry if my question was not clear: what I am looking for is:

 SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
 the same item_number and data_value=2

 Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier


-- 
Mogens Melander
+66 8701 33224


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

Thank you, 

 SELECT * FROM test
 WHERE item_number in (SELECT item_number FROM test where data_value=1)
 AND item_number not in (SELECT item_number FROM test where data_value = 2);

That did it.

Olivier


 On Wed, April 29, 2015 07:20, Olivier Nicole wrote:
 SELECT * FROM table WHERE item_number=1;

 Sorry if my question was not clear: what I am looking for is:

 SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
 the same item_number and data_value=2

 Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier


 -- 
 Mogens Melander
 +66 8701 33224


 -- 
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

Lucio, 

 I have a table where each record is made of one item_number and one
 data_value.
 You do not have any other column ? In particular you do not have any 
 unique key record identifier ? All my tables have a column with a record 
 sequence number seq int NOT NULL AUTO_INCREMENT which is also a key
 KEY auxiliary(seq). This is useful a posteriori to locate particular 
 records.

I do, but that was irrelevant to my question, as it is only counting the
records, it carries no information.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?
 1) by select you mean display at the terminal using the mysql line mode
 client, or locate all affected records for further work ?

I meant SELECT command, so a display I guess (but that would be the same
select in Perl).

 2) am I getting it correctly that you want to locate all the cases where
 a given item_number (any) has JUST ONE occurrence in the table ?

 In the line mode client this can be easily done with an additional table, 
 which can be a temporary table.

My idea was to do it in one single command, without using additional
table.

I ended up with something along the line of:

select handle, text_value from metadatavalue, handle where 
item_id in (select item_id from metadatavalue where metadata_field_id=64)
and item_id not in (select item_id from metadatavalue 
where metadata_field_id=27) 
and metadata_field_id=64 
and handle.resource_id=item_id 
and resource_type_id=2
order by item_id;

Maybe not the fastest nor the nicest, but as I need to run it only once,
it is enought.

Thank you,

Olivier

 Consider e.g. the following table (it has two columns, no seq column, and
 nothing else ... actually it is a table of seq pointers in two other 
 tables)

   select * from north33w1t7_ 

 | north33 | w1t7 |
 +-+--+
 |  21 |1 |
 |  21 |2 |
 |  24 |   20 |

 create temporary table temp1
 select north33,count(*) as c from north33w1t7_
 group by north33 order by north33;

 temp1 will contain something like this

 | north33 | c |
 +-+---+
 |  21 | 2 |
 |  24 | 1 |

 so it will tell you that item 21 has 2 counteparts, while item 24 
 has 1 counterpart.

 If you want to select (display) all cases in the main table with 1 
 counterpart do

 select north33w1t7_.*
 from temp1 join north33w1t7_  on 
 temp1.north33=north33w1t7_.north33
 where c=1 :

 | north33 | w1t7 |
 +-+--+
 |  24 |   20 |
 |  200013 |   93 |


 A different story would be if you want always to extract ONE record from 
 the main table, the single one if c=1, and the FIRST one if c1.

 What you define first it is up to you (the smallest data_value, the 
 highest data_value, a condition on other columns).

 Here in general I use a trick which involves one or two temporary tables 
 and a variable.  I initialize the variable to zero (or a value which is 
 not represented in the table, which shall be ordered on the columns as you 
 need. Then I test whether the item_number is the same as the variable, if 
 not I declare it to be first, then reset the variable in the same select 
 statement.

   set @x:=0;
   select north33w1t7_.*,
   if(@xnorth33w1t7_.north33,'FIRST','no') as flag,
   @x:=north33w1t7_.north33
   from temp1 join north33w1t7_  on temp1.north33=north33w1t7_.north33
   where c1 order by north33,w1t

 | north33 | w1t7 | flag  | @x:=north33w1t7_.north33 |
 +-+--+---+--+
 |  21 |1 | FIRST |   21 |
 |  21 |2 | no|   21 |
 |  22 |8 | FIRST |   22 |
 |  22 |9 | no|   22 |

 I can then save this select to a temporary table, and take my pick where 
 flag='FIRST'.


 of course you can also do without the join with temp1 if you want
 either the single or the first (i.e. c=1 and c1)

 select *,if(@xnorth33,'FIRST','no') as flag,@x:=north33 from 
 north33w1t7_  order by north33,w1t7

 | north33 | w1t7 | flag  | @x:=north33 |
 +-+--+---+-+
 |  21 |1 | FIRST |  21 |
 |  21 |2 | no|  21 |
 |  22 |8 | FIRST |  22 |
 |  22 |9 | no|  22 |
 |  24 |   20 | FIRST |  24 |


 -- 
 
 Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
 For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
 
 Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Mogens Melander]

SELECT * FROM table WHERE item_number=1;

On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier

-- 
Mogens Melander
+66 8701 33224


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Select one valuebut not the other

[Olivier Nicole]

 SELECT * FROM table WHERE item_number=1;

Sorry if my question was not clear: what I am looking for is:

SELECT * FROM table WHERE data_value=1 AND there is not any reccord with
the same item_number and data_value=2

Olivier


 On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
 Hi,

 I am sure that it is feasible with MySQl, and I am sure that's a newbie
 question, but my SQL skills are limited...

 I have a table where each record is made of one item_number and one
 data_value.

 If one item has several values, there are several records with the same
 item_number and a different data_value.

 What is the command to select all the records where an item_number has
 the data 1 but not the data 2?

 Thanks in advance,

 Olivier

 -- 
 Mogens Melander
 +66 8701 33224


 -- 
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is
 believed to be clean.

-- 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: SELECT subquery problem

[cl]

  You can do:
  
  
  SELECT last_name, first_name, phone, if(pub_email=Y,email,) as email
  FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y
  ORDER BY last_name ASC
  

Gracias, Carlos. This worked fine!

---Fritz


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: SELECT subquery problem

[Andrew Moore]

Try using a CASE construct in the select. Should work for this.

A


On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn stef...@web.de wrote:

 You cannot do this. A sql result alwas has the same number of columns in
 each row. You could have null or  in the column, though. This could be
 done via the if(,,)-statement of mysql or by using a union and two selects,
 one for pub_email=n and the other for the rest.


 Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr
 Von: cl c...@nimbleeye.com
 An: mysql@lists.mysql.com
 Betreff: SELECT subquery problem
 De-lurking here.

 I am trying to figure out how to return results from a query. What I need
 to do is to return 4 columns from a database. This is easy:

 SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE
 `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC

 This works fine, as expected.

 But, I want to only display the value in `email` if the value in another
 field, `pub_email` = Y So, the resultant output would look like this, for
 instance, if the value of `pub_email` =N for Mr. Wills:

 Jones John 555-555- johnjo...@nowhere.com
 Smith Jim 555-222- jimsm...@nothing.com
 Wills Chill 555-111-
 Zorro Felicity 555-999- felicityzo...@madeup.com

 Can't quite figure out how to express this.

 TIA for your suggestions!


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql[http://lists.mysql.com/mysql]




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Select data from large tables

[Adarsh Sharma]

More than 20163845 rows are there and my application continuously insert 
data in the table.

daily i think there is a increase in 2.5 Gb in that table.

Thanks

Chris Tate-Davies wrote:

How many rows is that???


On Tue, 2011-11-15 at 16:05 +0530, Adarsh Sharma wrote:
  

Dear all,

I have a doubt regarding fetching data from large tables.
I need to fetch selected columns from a 90Gb Table  5Gb index on it.

CREATE TABLE `content_table` (
  `c_id` bigint(20) NOT NULL DEFAULT '0',
  `link_level` tinyint(4) DEFAULT NULL,
  `u_id` bigint(20) NOT NULL,
  `heading` varchar(150) DEFAULT NULL,
  `category` varchar(150) DEFAULT NULL,
  `c_url` varchar(500) NOT NULL,
  `keywords` varchar(500) DEFAULT NULL,
  `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `content` mediumtext,
  PRIMARY KEY (`c_id`),
  KEY `idx_url` (`c_url`),
  KEY `idx_head` (`heading`),
  KEY `idx_dtstamp` (`dt_stamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now I need to select distict category from content_table of size 90Gb.

Simple select command can take days to complete  I donot think creating 
index on that column is a good idea.

Please let me know any ideas to do that.

Thanks




  

Re: Select data from large tables

[Johan De Meersman]

You might want to have a look at partitioning that table. It'll be a 
considerable downtime, but if done right it'll speed up a lot of your selects.

- Original Message -
 From: Adarsh Sharma adarsh.sha...@orkash.com
 To: mysql mysql@lists.mysql.com
 Sent: Tuesday, 15 November, 2011 11:39:41 AM
 Subject: Re: Select data from large tables
 
 More than 20163845 rows are there and my application continuously
 insert
 data in the table.
 daily i think there is a increase in 2.5 Gb in that table.
 
 Thanks
 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select ... into local outfile ... ???

[Dennis]

Thanks, Tyler, 
That's very helpful.
Dennis

--- On Sat, 9/3/11, Tyler Poland tpol...@engineyard.com wrote:

From: Tyler Poland tpol...@engineyard.com
Subject: Re: select ... into local outfile ... ???
To: mysql@lists.mysql.com
Date: Saturday, September 3, 2011, 3:45 AM

Dennis,

The following closely simulates the default INTO OUTFILE, you'll have to 
modify it slightly for your purposes:
mysql --delimiter=comma -N  select_statement.sql \
  | sed 's/\/\\\/g' \
  | sed 's/\t/\,\/g' \
  | sed 's/$/\/g' \
  | sed 's/^/\/g' \
  | sed 's/\NULL\/\\N/g' \
  | sed 's/\\t/\t/g' '  output.txt


Tyler
*

*On 9/2/11 3:40 PM, Dennis wrote:
 hi, there,
 the following is my sql statement:
 SELECT   HIGH_PRIORITY   SQL_BIG_RESULT   SQL_NO_CACHE
    tb.url_sign,  m_url,    m_title,   m_weight    INTO 
OUTFILE   '/tmp/a.csv'  FIELDS TERMINATED BY ','  ENCLOSED BY '\'   LINES  
TERMINATED BY '\n' STARTING BY '='
 FROM d_local.ta, d_news.tbWHERE ta.url_sign = tb.url_sign
 
 Before, the server and the client were on the same machine; now, I need to 
 generate the output file on client (which is on a different machine from the 
 server). But it seams that there is no select ... into LOCAL file 
 statement.Any suggestion is appreciated.
 Dennis
 

Re: select ... into local outfile ... ???

[Hal�sz S�ndor]

 2011/09/03 03:40 +0800, Dennis 
But it seems that there is no select ... into LOCAL file statement. Any 
suggestion is appreciated. 

Indeed: you can use only standard output.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select ... into local outfile ... ???

[Tyler Poland]

Dennis,

The following closely simulates the default INTO OUTFILE, you'll have 
to modify it slightly for your purposes:

mysql --delimiter=comma -N  select_statement.sql \
  | sed 's/\/\\\/g' \
  | sed 's/\t/\,\/g' \
  | sed 's/$/\/g' \
  | sed 's/^/\/g' \
  | sed 's/\NULL\/\\N/g' \
  | sed 's/\\t/\t/g' '  output.txt


Tyler
*

*On 9/2/11 3:40 PM, Dennis wrote:

hi, there,
the following is my sql statement:
SELECT   HIGH_PRIORITY   SQL_BIG_RESULT   SQL_NO_CACHE
   tb.url_sign,  m_url,m_title,   m_weightINTO OUTFILE   '/tmp/a.csv'  
FIELDS TERMINATED BY ','  ENCLOSED BY '\'   LINES  TERMINATED BY '\n' STARTING 
BY '='
FROM d_local.ta, d_news.tbWHERE ta.url_sign = tb.url_sign

Before, the server and the client were on the same machine; now, I need to generate the 
output file on client (which is on a different machine from the server). But it seams 
that there is no select ... into LOCAL file statement.Any suggestion is 
appreciated.
Dennis

Re: SELECT records less than 15 minutes old

[walter harms]

from:http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
SET GLOBAL time_zone = timezone;

from:http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_utc-timestamp
* UTC_TIMESTAMP, UTC_TIMESTAMP()

I have the same results, according to the docs timezone is the offset to UTC 
(what is used internaly of cause).
see also: 
http://www.mysqlfaqs.net/mysql-faqs/General-Questions/How-to-manage-Time-Zone-in-MySQL

btw: please notice the difference between:

mysql select @@session.time_zone ;
+-+
| @@session.time_zone |
+-+
| +00:00  |
+-+
1 row in set (0.00 sec)

mysql select @@global.time_zone ;
++
| @@global.time_zone |
++
| SYSTEM |
++
1 row in set (0.00 sec)



ntl personally i would say it should work with UTC but someone decided 
otherwise.

(i just found a hint why: 
http://dev.mysql.com/doc/refman/4.1/en/mysql-tzinfo-to-sql.html )


hope that helps,
 wh

Am 21.06.2011 00:00, schrieb sono...@fannullone.us:
 On Jun 20, 2011, at 10:11 AM, Jerry Schwartz wrote:
 
 You should use UTC time zone or you will run into trouble with DST.

 [JS] If you do that, you can't use an automatic timestamp field. You have to 
 set the field yourself.
 
   Thanks Walter and Jerry.
 
   Is there a way to get NOW() to use UTC instead of the server timezone?  
 (The server is not mine, so I can't change the my.cnf.)  Here's my statement:
 
 SELECT * FROM `log` WHERE `id` = $_id AND ( `time_stamp` = DATE_SUB(NOW(), 
 INTERVAL 30 MINUTE) )
 
   Earlier in my PHP script I've used date_default_timezone_set, but that 
 doesn't affect the MySQL statement.
 
 --
 
 Possible Solution
 
 I tried: SET time_zone = 'UTC'; 
 but MySQL complained with: #1298 - Unknown or incorrect time zone: 'UTC'
 
 I then tried:
 SET time_zone = '-0:00'; 
 and that seems to have worked.  Is this the correct way to do it?
 
 Thanks,
 Marc

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT records less than 15 minutes old

[Jerry Schwartz]

snip

-Original Message-
From: sono...@fannullone.us [mailto:sono...@fannullone.us]
Sent: Monday, June 20, 2011 6:01 PM
To: mysql@lists.mysql.com
Cc: wha...@bfs.de; Jerry Schwartz
Subject: Re: SELECT records less than 15 minutes old

On Jun 20, 2011, at 10:11 AM, Jerry Schwartz wrote:

 You should use UTC time zone or you will run into trouble with DST.

 [JS] If you do that, you can't use an automatic timestamp field. You have 
 to
 set the field yourself.

   Thanks Walter and Jerry.

   Is there a way to get NOW() to use UTC instead of the server timezone?
(The server is not mine, so I can't change the my.cnf.)  Here's my statement:

SELECT * FROM `log` WHERE `id` = $_id AND ( `time_stamp` = DATE_SUB(NOW(),
INTERVAL 30 MINUTE) )


   Earlier in my PHP script I've used date_default_timezone_set, but that
doesn't affect the MySQL statement.

--

Possible Solution

I tried: SET time_zone = 'UTC';
but MySQL complained with: #1298 - Unknown or incorrect time zone: 'UTC'

I then tried:
SET time_zone = '-0:00';
and that seems to have worked.  Is this the correct way to do it?


[JS] I believe that is the only way to do it.

The reason you can't use time zone names is that the time zone tables in MySQL 
have not been loaded.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT records less than 15 minutes old

[walter harms]

Am 19.06.2011 21:06, schrieb sono...@fannullone.us:
 On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:
 
 just a quick debug:
 
   Thanks, Claudio.  It turned out to be that NOW() was using the server's 
 time and my timestamp was based on my timezone.  After fixing that, the 
 SELECT statement works properly.
 
 Marc

You should use UTC time zone or you will run into trouble with DST.

re,
 wh

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT records less than 15 minutes old

[Jerry Schwartz]

-Original Message-
From: walter harms [mailto:wha...@bfs.de]
Sent: Monday, June 20, 2011 7:07 AM
To: sono...@fannullone.us
Cc: mysql@lists.mysql.com
Subject: Re: SELECT records less than 15 minutes old



Am 19.06.2011 21:06, schrieb sono...@fannullone.us:
 On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:

 just a quick debug:

  Thanks, Claudio.  It turned out to be that NOW() was using the server's
time and my timestamp was based on my timezone.  After fixing that, the 
SELECT
statement works properly.

 Marc

You should use UTC time zone or you will run into trouble with DST.

[JS] If you do that, you can't use an automatic timestamp field. You have to 
set the field yourself.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT records less than 15 minutes old

[sono-io]

On Jun 20, 2011, at 10:11 AM, Jerry Schwartz wrote:

 You should use UTC time zone or you will run into trouble with DST.
 
 [JS] If you do that, you can't use an automatic timestamp field. You have to 
 set the field yourself.

Thanks Walter and Jerry.

Is there a way to get NOW() to use UTC instead of the server timezone?  
(The server is not mine, so I can't change the my.cnf.)  Here's my statement:

SELECT * FROM `log` WHERE `id` = $_id AND ( `time_stamp` = DATE_SUB(NOW(), 
INTERVAL 30 MINUTE) )

Earlier in my PHP script I've used date_default_timezone_set, but that 
doesn't affect the MySQL statement.

--

Possible Solution

I tried: SET time_zone = 'UTC'; 
but MySQL complained with: #1298 - Unknown or incorrect time zone: 'UTC'

I then tried:
SET time_zone = '-0:00'; 
and that seems to have worked.  Is this the correct way to do it?

Thanks,
Marc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT records less than 15 minutes old

[Claudio Nanni]

just a quick debug:

SELECT time_stamp,DATE_SUB(NOW(), INTERVAL 15 MINUTE) FROM `records` WHERE
`order_id` = $order_id order by time_stamp desc limit 10;

what do you get?



2011/6/19 sono...@fannullone.us

 Hi,

I'm trying to write a statement that will return all records that
 match a particular order_id and that have a timestamp within the last 15
 minutes.  I thought that this should work:

 SELECT * FROM `records` WHERE `order_id` = $order_id AND (`time_stamp` =
 DATE_SUB(NOW(), INTERVAL 15 MINUTE))

 but it returns zero rows, no matter what.  If I up the interval to
 something huge, like 15000, it will then return records.  Very strange.
  It's almost like it's using seconds, not minutes.

Is my syntax wrong?

 Thanks,
 Marc
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com




-- 
Claudio

Re: SELECT records less than 15 minutes old

[sono-io]

On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:

 just a quick debug:

Thanks, Claudio.  It turned out to be that NOW() was using the server's 
time and my timestamp was based on my timezone.  After fixing that, the SELECT 
statement works properly.

Marc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select with counts of matching rows from another table...

[S�ndor Hal�sz]

 2011/03/23 16:29 -0700, Steffan A. Cline 
So, this is what I have as a basis to work on assuming I am polling
article #36.
 
Select *, if(b.articleid=36,1,0) as checked from groups g
Left join agmap a on g.groupid=a.groupid
Left join articles b on a.articleid=b.articleid
Order by g.groupname

Why not restrict it to '36': 'WHERE b.articleid = 36'?

As for the grouping, as it says in its helptext about MySQL s permissive 'GROUP 
BY', the choice for any thus allowed field --one forbidden by the standard-- is 
random. (It happens I have a case where that is just what I want: I do not care 
which value is shown, as long as it goes with the rest.) Maybe you want 
'articleid', too, in the grouping?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select with counts of matching rows from another table...

[Reindl Harald]

Am 31.03.2011 17:53, schrieb S?ndor Hal?sz:
 2011/03/23 16:29 -0700, Steffan A. Cline 
 So, this is what I have as a basis to work on assuming I am polling
 article #36.
  
 Select *, if(b.articleid=36,1,0) as checked from groups g
 Left join agmap a on g.groupid=a.groupid
 Left join articles b on a.articleid=b.articleid
 Order by g.groupname
 
 Why not restrict it to '36': 'WHERE b.articleid = 36'?
 
 As for the grouping, as it says in its helptext about MySQL s permissive 
 'GROUP BY', 
 the choice for any thus allowed field --one forbidden by the standard-- is 
 random. 
 (It happens I have a case where that is just what I want: I do not care which 
 value 
 is shown, as long as it goes with the rest.) Maybe you want 'articleid', too, 
 in the grouping?

instead of writing me since weeks private mails that you do not want
gpg-signed mails and how hard it is for you to not ignore .asc-attachments
you should learn to use your mail-client and how to quote answers

original-text followed by  is NOT quoting!



signature.asc
Description: OpenPGP digital signature

Re: SELECT Help

[Tompkins Neil]

Hi Travis,

That query kind of gives me the desired result.  However, if is showing
me 1, 18, 11, 23, 3, 2010-11-14 17:18:17 record and not 2, 11, 10, 3, 6,
2010-12-20 22:17:13, which is when they changed teams.  Any thoughts ?

Cheers
Neil

On Thu, Feb 3, 2011 at 10:32 PM, Travis Ard travis_...@hotmail.com wrote:

 Something like this might help you find all of the times where your user_id
 switched to a different team_id:

 select team_id, user_id, min(last_changed)
 from
 (select home_team_id as team_id, home_user_id as user_id, last_changed
 from data
 union all
 select away_team_id as team_id, away_user_id as user_id, last_changed
 from data) s1
 where s1.user_id = 3
 group by team_id, user_id;

 -Travis

 -Original Message-
 From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
 Sent: Thursday, February 03, 2011 6:34 AM
 To: [MySQL]
 Subject: SELECT Help

 Hi,

 I've the following list of sample data, and need a SELECT statement to help
 me identify the point at which I've highlighted the data :

 Season, Competition, home_team_id, away_team_id, home_user_id,
 away_user_id,
 last_changed
 1, 18, 11, 23, 3, 2010-11-14 17:18:17
 1, 11, 8, 3, 82, 2010-11-14 18:37:44
 1, 20, 11, 69, 3, 2010-11-17 23:07:49
 1, 1, 11, 4, 3, 2010-11-18 19:00:26
 1, 11, 1, 3, 4, 2010-11-18 19:00:42
 1, 12, 11, 5, 3, 2010-11-19 22:49:49
 1, 11, 14, 3, 19, 2010-11-23 21:38:19
 1, 3, 11, 15, 3, 2010-11-25 22:08:23
 1, 7, 11, 66, 3, 2010-11-28 02:38:15
 2, 73, 60, 137, 3, 2010-12-08 00:22:30
 2, 60, 73, 3, 137, 2010-12-08 00:22:35
 2, 60, 37, 3, 112, 2010-12-09 20:05:44
 2, 60, 65, 3, 158, 2010-12-12 21:45:14
 2, 72, 60, 141, 3, 2010-12-13 15:38:25
 2, 60, 68, 3, 87, 2010-12-13 16:08:08
 2, 60, 45, 3, 8, 2010-12-13 22:34:40
 2, 66, 60, 140, 3, 2010-12-14 22:10:42
 2, 60, 71, 3, 142, 2010-12-16 19:48:46
 2, 60, 64, 3, 30, 2010-12-19 16:41:21
 2, 76, 60, 17, 3, 2010-12-19 19:17:04
 2, 60, 76, 3, 17, 2010-12-20 00:40:56
 *2, 11, 10, 3, 6, 2010-12-20 22:17:13*
 2, 13, 11, 104, 3, 2010-12-21 00:36:37
 2, 6, 11, 168, 3, 2010-12-29 20:20:52
 2, 11, 18, 3, 97, 2010-12-29 20:41:07
 2, 20, 11, 5, 3, 2010-12-30 21:24:58
 2, 15, 11, 163, 3, 2010-12-30 21:46:39
 2, 13, 11, 12, 3, 2010-12-30 22:33:15

 Basically, I need to find the point in which the user for
 either home_user_id or away_user_id (in this instance 3) changed teams
 for home_team_id or away_team_id - if you understand what I mean ?  Any
 ideas on how I can achieve this using MySQL ?

 Cheers
 Neil

RE: SELECT Help

[Travis Ard]

Something like this might help you find all of the times where your user_id
switched to a different team_id:

select team_id, user_id, min(last_changed)
from
(select home_team_id as team_id, home_user_id as user_id, last_changed
from data
union all
select away_team_id as team_id, away_user_id as user_id, last_changed
from data) s1
where s1.user_id = 3
group by team_id, user_id;

-Travis

-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] 
Sent: Thursday, February 03, 2011 6:34 AM
To: [MySQL]
Subject: SELECT Help

Hi,

I've the following list of sample data, and need a SELECT statement to help
me identify the point at which I've highlighted the data :

Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id,
last_changed
1, 18, 11, 23, 3, 2010-11-14 17:18:17
1, 11, 8, 3, 82, 2010-11-14 18:37:44
1, 20, 11, 69, 3, 2010-11-17 23:07:49
1, 1, 11, 4, 3, 2010-11-18 19:00:26
1, 11, 1, 3, 4, 2010-11-18 19:00:42
1, 12, 11, 5, 3, 2010-11-19 22:49:49
1, 11, 14, 3, 19, 2010-11-23 21:38:19
1, 3, 11, 15, 3, 2010-11-25 22:08:23
1, 7, 11, 66, 3, 2010-11-28 02:38:15
2, 73, 60, 137, 3, 2010-12-08 00:22:30
2, 60, 73, 3, 137, 2010-12-08 00:22:35
2, 60, 37, 3, 112, 2010-12-09 20:05:44
2, 60, 65, 3, 158, 2010-12-12 21:45:14
2, 72, 60, 141, 3, 2010-12-13 15:38:25
2, 60, 68, 3, 87, 2010-12-13 16:08:08
2, 60, 45, 3, 8, 2010-12-13 22:34:40
2, 66, 60, 140, 3, 2010-12-14 22:10:42
2, 60, 71, 3, 142, 2010-12-16 19:48:46
2, 60, 64, 3, 30, 2010-12-19 16:41:21
2, 76, 60, 17, 3, 2010-12-19 19:17:04
2, 60, 76, 3, 17, 2010-12-20 00:40:56
*2, 11, 10, 3, 6, 2010-12-20 22:17:13*
2, 13, 11, 104, 3, 2010-12-21 00:36:37
2, 6, 11, 168, 3, 2010-12-29 20:20:52
2, 11, 18, 3, 97, 2010-12-29 20:41:07
2, 20, 11, 5, 3, 2010-12-30 21:24:58
2, 15, 11, 163, 3, 2010-12-30 21:46:39
2, 13, 11, 12, 3, 2010-12-30 22:33:15

Basically, I need to find the point in which the user for
either home_user_id or away_user_id (in this instance 3) changed teams
for home_team_id or away_team_id - if you understand what I mean ?  Any
ideas on how I can achieve this using MySQL ?

Cheers
Neil


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select data from two tables and SUM qty of the same ID

[haidarpesebe]

Thanks a lot. We will try and inform you.

--Original Message--
From: Aveek Misra
To: Albahar Uya
Cc: MySQL Lists
Subject: Re: select data from two tables and SUM qty of the same ID
Sent: Nov 10, 2010 17:38

SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY) as 
Total_Quantity FROM salesreport, product WHERE product.ID = salesreport.ID 
GROUP BY salesreport.ID ORDER BY Total_Quantity DESC;



On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:

 please help us look for the data select from two tables with the following 
 details:
 
 Primary table: product
 ID | ProductName | ISBN |
 ---
 1 | Product A  | 123 |
 2 | Product B  | 456 |
 3 | product C  | 444 |
 ---
 
 second table: salesreport
 IDS | ID | CITY  | QTY |
 
 1| 1 | New York | 3 |
 2| 1 | Alabama   | 5 |
 3| 1 | London| 1 |
 4| 1 | Jakarta| 5 |
 5| 2 | New York | 8 |
 6| 2 | Alabama   | 4 |
 7| 2 | London| 9 |
 8| 2 | Jakarta| 3 |
 --
 
 ID in table product same with ID table salesreport
 How we will show SALES REPORT product is sold only at the table salesreport 
 SUM qty to the top sold and unsold product C will not be displayed.
 
 The result will be like this:
 
 No. Product Name ISBN QTY
 
 1.   product B  45624
 2.   product A  12314
 
 
 I'm using PHP. Thanks a lot for information.



Sent from my BlackBerry®

Re: select data from two tables and SUM qty of the same ID

[Aveek Misra]

SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY) as 
Total_Quantity FROM salesreport, product WHERE product.ID = salesreport.ID 
GROUP BY salesreport.ID ORDER BY Total_Quantity DESC;



On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:

 please help us look for the data select from two tables with the following 
 details:
 
 Primary table: product
 ID | ProductName | ISBN |
 ---
 1 | Product A  | 123 |
 2 | Product B  | 456 |
 3 | product C  | 444 |
 ---
 
 second table: salesreport
 IDS | ID | CITY  | QTY |
 
 1| 1 | New York | 3 |
 2| 1 | Alabama   | 5 |
 3| 1 | London| 1 |
 4| 1 | Jakarta| 5 |
 5| 2 | New York | 8 |
 6| 2 | Alabama   | 4 |
 7| 2 | London| 9 |
 8| 2 | Jakarta| 3 |
 --
 
 ID in table product same with ID table salesreport
 How we will show SALES REPORT product is sold only at the table salesreport 
 SUM qty to the top sold and unsold product C will not be displayed.
 
 The result will be like this:
 
 No. Product Name ISBN QTY
 
 1.   product B  45624
 2.   product A  12314
 
 
 I'm using PHP. Thanks a lot for information.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select data from two tables and SUM qty of the same ID

[HaidarPesebe]

we've tried to use as your dialing purposes, but still can not.

This structure

SELECT product.product_id, product.isbn, product.name, product.author, 
product.harga, product.discount, sum(sales_report.qty) as totalqty FROM  . 
$prefix . _sales_report,  . $prefix . _product WHERE product.product_id = 
sales_report.bid GROUP BY sales_report.bid ORDER BY totalqty DESC


Display error on the screen like this:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result 
resource in E:\xampp\htdocs\test\inc\sqllayer.php on line 271

Unknown column 'product.product_id' in 'field list'

Note: product_id (product) = bid (sales_report)

haidarpesebe

- Original Message - 
From: Aveek Misra ave...@yahoo-inc.com

To: HaidarPesebe haidarpes...@gmail.com
Cc: MySQL Lists mysql@lists.mysql.com
Sent: Wednesday, November 10, 2010 5:38 PM
Subject: Re: select data from two tables and SUM qty of the same ID


SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY) 
as Total_Quantity FROM salesreport, product WHERE product.ID = 
salesreport.ID GROUP BY salesreport.ID ORDER BY Total_Quantity DESC;




On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:

please help us look for the data select from two tables with the following 
details:


Primary table: product
ID | ProductName | ISBN |
---
1 | Product A  | 123 |
2 | Product B  | 456 |
3 | product C  | 444 |
---

second table: salesreport
IDS | ID | CITY  | QTY |

1| 1 | New York | 3 |
2| 1 | Alabama   | 5 |
3| 1 | London| 1 |
4| 1 | Jakarta| 5 |
5| 2 | New York | 8 |
6| 2 | Alabama   | 4 |
7| 2 | London| 9 |
8| 2 | Jakarta| 3 |
--

ID in table product same with ID table salesreport
How we will show SALES REPORT product is sold only at the table 
salesreport SUM qty to the top sold and unsold product C will not be 
displayed.


The result will be like this:

No. Product Name ISBN QTY

1.   product B  45624
2.   product A  12314


I'm using PHP. Thanks a lot for information.



__ Information from ESET Smart Security, version of virus signature 
database 5608 (20101110) __


The message was checked by ESET Smart Security.

http://www.eset.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select data from two tables and SUM qty of the same ID

[HaidarPesebe]

I tried this and succeeded. Thank you

SELECT product_id, isbn, name, author, harga, discount, sum(qty) as totalqty
FROM  . $prefix . _sales_report b INNER JOIN  . $prefix . _product p ON
(p.product_id = b.bid) GROUP BY b.bid ORDER BY totalqty DESC
haidarpesebe
- Original Message - 
From: Aveek Misra ave...@yahoo-inc.com

To: HaidarPesebe haidarpes...@gmail.com
Cc: MySQL Lists mysql@lists.mysql.com
Sent: Wednesday, November 10, 2010 5:38 PM
Subject: Re: select data from two tables and SUM qty of the same ID


SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY) 
as Total_Quantity FROM salesreport, product WHERE product.ID = 
salesreport.ID GROUP BY salesreport.ID ORDER BY Total_Quantity DESC;




On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:

please help us look for the data select from two tables with the following 
details:


Primary table: product
ID | ProductName | ISBN |
---
1 | Product A  | 123 |
2 | Product B  | 456 |
3 | product C  | 444 |
---

second table: salesreport
IDS | ID | CITY  | QTY |

1| 1 | New York | 3 |
2| 1 | Alabama   | 5 |
3| 1 | London| 1 |
4| 1 | Jakarta| 5 |
5| 2 | New York | 8 |
6| 2 | Alabama   | 4 |
7| 2 | London| 9 |
8| 2 | Jakarta| 3 |
--

ID in table product same with ID table salesreport
How we will show SALES REPORT product is sold only at the table 
salesreport SUM qty to the top sold and unsold product C will not be 
displayed.


The result will be like this:

No. Product Name ISBN QTY

1.   product B  45624
2.   product A  12314


I'm using PHP. Thanks a lot for information. 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select NICE

[mos]

At 10:49 AM 9/29/2010, Steve Staples wrote:

Google has not been kind to me on this one, so I figured I would ask
here...

how can I select with NICE options, so that it doesn't KILL my server,
or any other queries...

Do you understand what I am asking?

Steve



Steve,

You might look at http://www.databasedesign-resource.com/mysql-tuning.html.
It mentions nice option near the bottom of the document. I've never used 
it myself.


http://www.google.ca/#hl=enbiw=1440bih=684q=mysql+renice+priorityaq=faqi=aql=oq=gs_rfai=fp=1558102cc0a7bff1

Mike 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT WHERE IN help

[Johan De Meersman]

If there are two, you will return two.

On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com
 wrote:

 Hi

 With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
 return two records for the record_id 3 ?  Is it possible ?

 Cheers
 Neil




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: SELECT WHERE IN help

[Tompkins Neil]

Thanks for the quick reply.  Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table.  However, because 3 exists twice
within  (3,4,5,6,7,3), I want it to return two records for record_id 3.  Is
it possible ?

Cheers
Neil

On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 If there are two, you will return two.


 On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi

 With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
 return two records for the record_id 3 ?  Is it possible ?

 Cheers
 Neil




 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel

Re: SELECT WHERE IN help

[Johan De Meersman]

I don't think that'll work, no. Why would you want to return duplicate data
? The whole point of an RDBMS is to *avoid* duplicate data :-)

On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com
 wrote:

 Thanks for the quick reply.  Basically in (3,4,5,6,7,3) the record_id of 3
 only exists once in the table my_table.  However, because 3 exists twice
 within  (3,4,5,6,7,3), I want it to return two records for record_id 3.
  Is it possible ?

 Cheers
 Neil


 On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 If there are two, you will return two.


 On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi

 With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
 return two records for the record_id 3 ?  Is it possible ?

 Cheers
 Neil




 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel





-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: SELECT WHERE IN help

[Tompkins Neil]

Thanks for the reply.  The search of  (3,4,5,6,7,3)  is pulling data from a
table.  I think in this case I need to change my design .

On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 I don't think that'll work, no. Why would you want to return duplicate data
 ? The whole point of an RDBMS is to *avoid* duplicate data :-)


 On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Thanks for the quick reply.  Basically in (3,4,5,6,7,3) the record_id of
 3 only exists once in the table my_table.  However, because 3 exists
 twice within  (3,4,5,6,7,3), I want it to return two records for
 record_id 3.  Is it possible ?

 Cheers
 Neil


 On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 If there are two, you will return two.


 On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi

 With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can
 I
 return two records for the record_id 3 ?  Is it possible ?

 Cheers
 Neil




 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel





 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel

Re: SELECT WHERE IN help

[Mark Goodge]

On 21/09/2010 16:44, Tompkins Neil wrote:

Thanks for the quick reply.  Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table.  However, because 3 exists twice
within  (3,4,5,6,7,3), I want it to return two records for record_id 3.  Is
it possible ?


No, that isn't possible.

Why do you want a duplicate record to be retrieved? There may be a 
better way of doing it.


Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT WHERE IN help

[Joerg Bruehe]

Hi Neil, all!


Tompkins Neil wrote:
 Hi
 
 With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
 return two records for the record_id 3 ?  Is it possible ?

This is a case where you may safely use natural language and logic. The
command is

  SELECT all fields FROM the records in mytable FOR WHICH THE
FOLLOWING CONDITION IS TRUE:
  the field record_id has a value which is IN the list 3, 4, 5, 6,
7, 3

The condition can only evaluate to true or false (ignoring NULL
values and the unknown truth value for now), and for that evaluation
it does not matter whether a matching value appears in your list only
once or repeatedly.

To achieve your desired effect, you might use a generator to create a
UNION statement. Roughly, the approach would be (+= means appending to
a string):

  value = first value of the list;
  statement = SELECT * FROM my_table WHERE record_id = $value;

  while (there are more values in the list)
  do
  value = next value of the list;
  statement +=
  UNION SELECT * FROM my_table WHERE record_id = $value;
  done;

  statement += ;;

  execute statement;

Obviously, this will create a huge statement if the value list is long,
and it doesn't seem to be efficient, so I don't recommend this technique
in general.

Before going that route, you should question your assumptions: Why is it
necessary to return the same record twice?


Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V.  Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT WHERE IN help

[Jerry Schwartz]

-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Tuesday, September 21, 2010 11:48 AM
To: Johan De Meersman
Cc: [MySQL]
Subject: Re: SELECT WHERE IN help

Thanks for the reply.  The search of  (3,4,5,6,7,3)  is pulling data from a
table.  I think in this case I need to change my design .

[JS] You can accomplish your goal by using a sub-select to create a table 
that has 3 in it twice, and then JOIN it to the original table.

As for why you would want to do this, that's another story. It sounds like you 
went down the wrong road.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com




On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 I don't think that'll work, no. Why would you want to return duplicate data
 ? The whole point of an RDBMS is to *avoid* duplicate data :-)


 On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Thanks for the quick reply.  Basically in (3,4,5,6,7,3) the record_id of
 3 only exists once in the table my_table.  However, because 3 exists
 twice within  (3,4,5,6,7,3), I want it to return two records for
 record_id 3.  Is it possible ?

 Cheers
 Neil


 On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman
vegiv...@tuxera.bewrote:

 If there are two, you will return two.


 On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil 
 neil.tompk...@googlemail.com wrote:

 Hi

 With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can
 I
 return two records for the record_id 3 ?  Is it possible ?

 Cheers
 Neil




 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel





 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select ROW_COUNT() INTO a variable

[Shawn Green (MySQL)]

On 7/19/2010 5:22 PM, Bryan Cantwell wrote:

I have a stored procedure in mysql 5.1.48 that deletes old data from my
tables.
I would like to keep a running count while it does this. Here is what I
try now:
...
DECLARE dropCnt INT DEFAULT 0;
...
SET @sql = CONCAT('DELETE FROM  myTable WHERE itemid = ', iID, ' AND
clock BETWEEN 0 AND ', histUnixTime, '  ');
  PREPARE s1 FROM @sql;
  EXECUTE s1;
  SELECT ROW_COUNT() INTO dropCnt;
  DEALLOCATE PREPARE s1;
...

The problem here is that SELECT ROW_COUNT() INTO dropCnt; returns NULL
every time.
If I just do the select without the INTO, I get 0 or some actual count
of rows. 
How can I get my dropCnt variable correcly set?




You may be able to use one of the other two syntaxes available:

1) SET dropCnt = ROW_COUNT()

2) SELECT dropCnt := ROW_COUNT()

Please let us know if either of those also fails.

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select w/ group by question

[Peter Brawley]

 Scott,


I would like to obtain the least cost of each product type and its
associated vendor.


See Within-group aggregates at 
http://www.artfulsoftware.com/infotree/queries.php.


PB

-

On 7/14/2010 9:25 AM, Scott Mullen wrote:

I'm having trouble formulating a query to gather the following data.  I can
do this via a script, but now it is more or less just bothering me if there
is an easy/efficient way to gather the following data from a single query.

Example Tables


Products
Type Cost Vendor_id
--
---
apple11
apple32
apple73
pear  2   1
pear  42
pear  23

Vendors
Vendor_id   Vendor_name

1  Walmart
2  Target
3  Kmart


I would like to obtain the least cost of each product type and its
associated vendor.

So...I would like to see a result similiar to the following:

Type  CostVendor_id Vendor_name
apple 1   1  Walmart
pear   2   1  Walmart
(Note: both vendors 1 and 3 have the same cost which is the lowest.  I'm not
really concerned with which vendor is chosen in the result set here.)


If I do:  select a.type, min(a.cost), a.vendor_id, b.vendor_name from
products a join vendors b on a.vendor_id = b.vendor_id group by a.type,
a.vendor_id, b.vendor_name all rows are returned because the
type/vendor_id/vendor_name are unique amongst each row.  If you remove the
vendor_id and vendor_name from the group by, you get a single row with the
lowest cost for each product, but the vendor_id's and vendor_name's are
incorrect because you are not grouping by them.

Is there a way to do this from a single query.  I know I can concat things
together and imbed a select in my where clause to get the result I want, but
this is horribly inefficient.
My real tables have somewhere around 30 million rows in them.

Thanks

Scott




No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10 
06:36:00



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select w/ group by question

[Michael Satterwhite]

On Wednesday, July 14, 2010 09:25:22 am Scott Mullen wrote:
 I'm having trouble formulating a query to gather the following data.  I can
 do this via a script, but now it is more or less just bothering me if there
 is an easy/efficient way to gather the following data from a single query.
 
 Example Tables
 
 
 Products
 Type Cost Vendor_id
 --
 ---
 apple11
 apple32
 apple73
 pear  2   1
 pear  42
 pear  23
 
 Vendors
 Vendor_id   Vendor_name
 
 1  Walmart
 2  Target
 3  Kmart
 
 
 I would like to obtain the least cost of each product type and its
 associated vendor.
 
 So...I would like to see a result similiar to the following:
 
 Type  CostVendor_id Vendor_name
 apple 1   1  Walmart
 pear   2   1  Walmart
 (Note: both vendors 1 and 3 have the same cost which is the lowest.  I'm
 not really concerned with which vendor is chosen in the result set here.)
 
 

Try this:

   select name, product_type, min(cost) from vendors join products on
   vendors.id = products.vendor_id group by product_type;

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Select w/ group by question

[Martin Gainty]

 


 Date: Wed, 14 Jul 2010 10:25:22 -0400
 Subject: Select w/ group by question
 From: smulle...@gmail.com
 To: mysql@lists.mysql.com
 
 I'm having trouble formulating a query to gather the following data. I can
 do this via a script, but now it is more or less just bothering me if there
 is an easy/efficient way to gather the following data from a single query.
 
 Example Tables
 
 
 Products
 Type Cost Vendor_id
 --
 ---
 apple 1 1
 apple 3 2
 apple 7 3
 pear 2 1
 pear 4 2
 pear 2 3
 
 Vendors
 Vendor_id Vendor_name
 
 1 Walmart
 2 Target
 3 Kmart
 
 
 I would like to obtain the least cost of each product type and its
 associated vendor.
 
 So...I would like to see a result similiar to the following:
 
 Type Cost Vendor_id Vendor_name
 apple 1 1 Walmart
 pear 2 1 Walmart
 (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not
 really concerned with which vendor is chosen in the result set here.)
 
 
 If I do: 

select a.type, min(a.cost), a.vendor_id, b.vendor_name from
products a join 

vendors b 

on a.vendor_id = b.vendor_id 

group by a.type,a.vendor_id, b.vendor_name 

 

all rows are returned because the type/vendor_id/vendor_name are unique amongst 
each row. 

 

If you remove the vendor_id and vendor_name from the group by, 

 

select a.type, min(a.cost), a.vendor_id,b.vendor_name from
products a join vendors b 

on a.vendor_id = b.vendor_id 

group by a.type;

 

you get a single row with the lowest cost for each product, 

but the vendor_id's and vendor_name's are incorrect because you are not 
grouping by them.
 
 Is there a way to do this from a single query. I know I can concat things
 together and imbed a select in my where clause to get the result I want, but
 this is horribly inefficient.
 My real tables have somewhere around 30 million rows in them.

MGput ascending indexes on vendor_id and vendor_name columns

MGreorg the tables so the rows will be in vendor_name (within vendor_id) order

 

MGreselect

MGselect a.type, min(a.cost), a.vendor_id,b.vendor_name from
MGproducts a join vendors b 

MGon a.vendor_id = b.vendor_id 

MGorder by a.type;


 
 Thanks
 
 Scott

  
_
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with 
Hotmail. 
http://www.windowslive.com/campaign/thenewbusy?tile=multicalendarocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5

Re: Select w/ group by question

[Scott Mullen]

Peter

Thanks for the link.  I've never run across this page before, but it has
tons of useful informationas well as several answers on how to implement
what I was trying to do.

Ended up going with a solution similar to this example (from the page you
referenced):

SELECT
  item,
  SUBSTR( MIN( CONCAT( LPAD(price,6,0),supplier) ), 7)   AS MinSupplier,
LEFT( MIN( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MinPrice,
  SUBSTR( MAX( CONCAT( LPAD(price,6,0),supplier) ), 7)   AS MaxSupplier,
LEFT( MAX( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MaxPrice
FROM  products
GROUP BY item;

Pretty straight forward and does not require another join back to the same
table with 30+ million rows.

Thanks

Scott

On Wed, Jul 14, 2010 at 10:35 AM, Peter Brawley peter.braw...@earthlink.net
 wrote:

  Scott,


  I would like to obtain the least cost of each product type and its
 associated vendor.


 See Within-group aggregates at
 http://www.artfulsoftware.com/infotree/queries.php.

 PB

 -


 On 7/14/2010 9:25 AM, Scott Mullen wrote:

 I'm having trouble formulating a query to gather the following data.  I
 can
 do this via a script, but now it is more or less just bothering me if
 there
 is an easy/efficient way to gather the following data from a single query.

 Example Tables


 Products
 Type Cost Vendor_id
 --
 ---
 apple11
 apple32
 apple73
 pear  2   1
 pear  42
 pear  23

 Vendors
 Vendor_id   Vendor_name
 
 1  Walmart
 2  Target
 3  Kmart


 I would like to obtain the least cost of each product type and its
 associated vendor.

 So...I would like to see a result similiar to the following:

 Type  CostVendor_id Vendor_name
 apple 1   1  Walmart
 pear   2   1  Walmart
 (Note: both vendors 1 and 3 have the same cost which is the lowest.  I'm
 not
 really concerned with which vendor is chosen in the result set here.)


 If I do:  select a.type, min(a.cost), a.vendor_id, b.vendor_name from
 products a join vendors b on a.vendor_id = b.vendor_id group by a.type,
 a.vendor_id, b.vendor_name all rows are returned because the
 type/vendor_id/vendor_name are unique amongst each row.  If you remove the
 vendor_id and vendor_name from the group by, you get a single row with the
 lowest cost for each product, but the vendor_id's and vendor_name's are
 incorrect because you are not grouping by them.

 Is there a way to do this from a single query.  I know I can concat things
 together and imbed a select in my where clause to get the result I want,
 but
 this is horribly inefficient.
 My real tables have somewhere around 30 million rows in them.

 Thanks

 Scott




 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10
 06:36:00

Re: SELECT and INSERT if no row is returned

[Johnny Withers]

Make parent_id a unique key. Doing a select first and inserting if no
result will work 99.9% of the time; however, consider 2 rows being
inserted at the same time.

JW

On Tuesday, March 23, 2010, Kyong Kim kykim...@gmail.com wrote:
 I need to INSERT a row into a table only if it does not exist to
 insure that there won't be duplicate rows.
 Due to the the usage case and the design of the primary key,
 non-unique id + auto increment, I don't think insert ignore is an
 option.

 What would be simplest and cheapest way to make sure that given the
 following schema

 create table some_table
 ( parent_id int //non-unique
   seq_id int auto_increment ) ENGINE=INNODB

 that no row with the same parent_id can be inserted?

 Kyong

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=joh...@pixelated.net



-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT and INSERT if no row is returned

[Rodrigo Ferreira]

First, if you want no duplicate parent_id, make it unique key (as JW saids). 
Look at INSERT ... ON DUPLICATE KEY UPDATE, I think this will solve the problem 
with one statement.

Rodrigo Ferreira

--- On Wed, 3/24/10, Johnny Withers joh...@pixelated.net wrote:

From: Johnny Withers joh...@pixelated.net
Subject: Re: SELECT and INSERT if no row is returned
To: Kyong Kim kykim...@gmail.com
Cc: mysql mysql@lists.mysql.com
Date: Wednesday, March 24, 2010, 9:32 AM

Make parent_id a unique key. Doing a select first and inserting if no
result will work 99.9% of the time; however, consider 2 rows being
inserted at the same
 time.

JW

On Tuesday, March 23, 2010, Kyong Kim kykim...@gmail.com wrote:
 I need to INSERT a row into a table only if it does not exist to
 insure that there won't be duplicate rows.
 Due to the the usage case and the design of the primary key,
 non-unique id + auto increment, I don't think insert ignore is an
 option.

 What would be simplest and cheapest way to make sure that given the
 following schema

 create table some_table
 ( parent_id int //non-unique
   seq_id int auto_increment ) ENGINE=INNODB

 that no row with the same parent_id can be inserted?

 Kyong

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=joh...@pixelated.net



-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=rodrigof_si...@yahoo.com




  

Re: SELECT and INSERT if no row is returned

[Kyong Kim]

I needed to give greater detail.

parent_id isn't unique. The table has a composite primary key (parent_id,
seq_id).
Here's a better schema def

CREATE TABLE sometable (
parent_id INT(10) NOT NULL,
seq_id INT(10) AUTO_INCREMENT,
child_id INT(10) NULL,
PRIMARY KEY(parent_id, seq_id),
UNIQUE KEY(child_id)
) ENGINE=INNODB;

The requirement is that there can be only 1 parent_id associated with a
given child or there can be only one parent_id not associated with a
child_id (NULL child_id). I need to avoid a race condition where 2
connections can SELECT and return an empty row and insert rows of the same
parent_id not associated with a message_id. It's that .1% of the cases we
want to avoid.

Kyong

On Wed, Mar 24, 2010 at 6:26 AM, Rodrigo Ferreira
rodrigof_si...@yahoo.comwrote:

 First, if you want no duplicate parent_id, make it unique key (as JW
 saids). Look at INSERT ... ON DUPLICATE KEY UPDATE, I think this will solve
 the problem with one statement.

 Rodrigo Ferreira

 --- On *Wed, 3/24/10, Johnny Withers joh...@pixelated.net* wrote:


 From: Johnny Withers joh...@pixelated.net
 Subject: Re: SELECT and INSERT if no row is returned
 To: Kyong Kim kykim...@gmail.com
 Cc: mysql mysql@lists.mysql.com
 Date: Wednesday, March 24, 2010, 9:32 AM

 Make parent_id a unique key. Doing a select first and inserting if no
 result will work 99.9% of the time; however, consider 2 rows being
 inserted at the same time.

 JW

 On Tuesday, March 23, 2010, Kyong Kim kykim...@gmail.com wrote:
  I need to INSERT a row into a table only if it does not exist to
  insure that there won't be duplicate rows.
  Due to the the usage case and the design of the primary key,
  non-unique id + auto increment, I don't think insert ignore is an
  option.
 
  What would be simplest and cheapest way to make sure that given the
  following schema
 
  create table some_table
  ( parent_id int //non-unique
seq_id int auto_increment ) ENGINE=INNODB
 
  that no row with the same parent_id can be inserted?
 
  Kyong
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
 
 

 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=rodrigof_si...@yahoo.com

Re: SELECT and INSERT if no row is returned

[Shawn Green]

Kyong Kim wrote:

I needed to give greater detail.

parent_id isn't unique. The table has a composite primary key (parent_id,
seq_id).
Here's a better schema def

CREATE TABLE sometable (
parent_id INT(10) NOT NULL,
seq_id INT(10) AUTO_INCREMENT,
child_id INT(10) NULL,
PRIMARY KEY(parent_id, seq_id),
UNIQUE KEY(child_id)
) ENGINE=INNODB;

The requirement is that there can be only 1 parent_id associated with a
given child or there can be only one parent_id not associated with a
child_id (NULL child_id). I need to avoid a race condition where 2
connections can SELECT and return an empty row and insert rows of the same
parent_id not associated with a message_id. It's that .1% of the cases we
want to avoid.



What you are describing is a UNIQUE key based on the combination of 
parent_id and child_id.


ALTER TABLE sometable ADD UNIQUE(parent_id, child_id);

Based on your descriptions, that should fix your duplication problems.

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select daily random

[Jason Carson]

 At 08:59 PM 2/27/2010, you wrote:
Hello everyone,

How would I select a random row that changes daily?

Thanks

 The common way would be to do:

 select * from table order by rand() limit 1;

 You can of course add a Where clause to select only those rows that were
 added today.

 select * from table where Log_Date=Date(Now()) order by rand() limit 1;

 This works fine as long as there are not too many dates to sort. Otherwise
 you will need to use an autoinc column and choose one of those randomly.
 This is not as easy as it looks because the sequence may have holes in it
 and may not be in the proper sequence.


 Mike


Thanks for the reply Mike but the common way you mentioned didn't do
what I wanted. I did some searching on Google and found the following
PHP/MySQL code which seems to do what I want...

$query = SELECT * FROM table ORDER BY rand( . date(Ymd) . ) LIMIT 1;

...It selects a random row that changes on a daily bases.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select daily random

[Jason Carson]

...I am using PHP 5.2

 Hello everyone,

 How would I select a random row that changes daily?

 Thanks



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=ja...@jasoncarson.ca





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: select daily random

[mos]

At 08:59 PM 2/27/2010, you wrote:

Hello everyone,

How would I select a random row that changes daily?

Thanks


The common way would be to do:

select * from table order by rand() limit 1;

You can of course add a Where clause to select only those rows that were 
added today.


select * from table where Log_Date=Date(Now()) order by rand() limit 1;

This works fine as long as there are not too many dates to sort. Otherwise 
you will need to use an autoinc column and choose one of those randomly. 
This is not as easy as it looks because the sequence may have holes in it 
and may not be in the proper sequence.



Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Select from remote server from stored procedure

[Neil Aggarwal]

 Is this possible to do?  To make a connection, inside the 
 stored procedure
 to a completely different machine and access the mysql there?

The only way I know to access tables from different servers
from a single connection is federated tables:
http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

Once you do that, you are accessing it like a local table.

I hope this helps.

Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select from remote server from stored procedure

[Johan De Meersman]

Posted this before, but beware: federated tables do NOT use indices. Every
select is a full table scan, and if you're talking about a logging table
that could become very expensive very fast.

On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal n...@jammconsulting.comwrote:

  Is this possible to do?  To make a connection, inside the
  stored procedure
  to a completely different machine and access the mysql there?

 The only way I know to access tables from different servers
 from a single connection is federated tables:
 http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

 Once you do that, you are accessing it like a local table.

 I hope this helps.

Neil

 --
 Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
 Host your MySQL database on a CentOS VPS for $25/mo
 Unmetered bandwidth = no overage charges, 7 day free trial


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be

Re: Select from remote server from stored procedure

[Harrison Fisk]

Hello Johan,

On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:

Posted this before, but beware: federated tables do NOT use indices.  
Every
select is a full table scan, and if you're talking about a logging  
table

that could become very expensive very fast.


This is not entirely true.  If you define an index on the local  
federated table, and it makes sense to use it, then a remote WHERE  
clause will be passed through and hence use the remote index.  Not all  
types of index accesses can be passed through such as this, however  
for a single row lookup on a primary key, it should be fine.


It is still not as fast as local access, but it's not as bad as always  
doing a full table scan remotely.


On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal  
n...@jammconsulting.comwrote:



Is this possible to do?  To make a connection, inside the
stored procedure
to a completely different machine and access the mysql there?


The only way I know to access tables from different servers
from a single connection is federated tables:
http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

Once you do that, you are accessing it like a local table.

I hope this helps.

  Neil


Regards,

Harrison
--
Harrison C. Fisk, MySQL Staff Support Engineer
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Select from remote server from stored procedure

[Steven Staples]

So what I am reading, I guess it would be safer to just do it how I am
currently doing it, as it really isn't that slow... it's just duplicating
the data elsewhere (I suppose maybe making this a slave table to the other
server... nah... lots of work there :P)

Thanks, and I did search it before, but I guess my searching keywords were
insufficient ;)


Steven Staples


-Original Message-
From: harrison.f...@sun.com [mailto:harrison.f...@sun.com] 
Sent: December 9, 2009 2:07 PM
To: Johan De Meersman
Cc: Neil Aggarwal; Steven Staples; mysql@lists.mysql.com
Subject: Re: Select from remote server from stored procedure

Hello Johan,

On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:

 Posted this before, but beware: federated tables do NOT use indices.  
 Every
 select is a full table scan, and if you're talking about a logging  
 table
 that could become very expensive very fast.

This is not entirely true.  If you define an index on the local  
federated table, and it makes sense to use it, then a remote WHERE  
clause will be passed through and hence use the remote index.  Not all  
types of index accesses can be passed through such as this, however  
for a single row lookup on a primary key, it should be fine.

It is still not as fast as local access, but it's not as bad as always  
doing a full table scan remotely.

 On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal  
 n...@jammconsulting.comwrote:

 Is this possible to do?  To make a connection, inside the
 stored procedure
 to a completely different machine and access the mysql there?

 The only way I know to access tables from different servers
 from a single connection is federated tables:
 http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

 Once you do that, you are accessing it like a local table.

 I hope this helps.

   Neil

Regards,

Harrison
-- 
Harrison C. Fisk, MySQL Staff Support Engineer
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.709 / Virus Database: 270.14.97/2550 - Release Date: 12/09/09
02:32:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Select from remote server from stored procedure

[Neil Aggarwal]

Steve:

 I suppose maybe making this a slave table 
 to the other
 server... nah... lots of work there

Setting your local server to be a slave of the
remote server is not too hard and would
be a MUCH better solution.

The steps are fairly staightforward:

1. Add a slave user to the remote database
2. Tell the remote server to create a binary
log
3. Tell the local server to be a slave of
the remote
4. Start the slave

It should take less than 1 hour to set it up.
I have done it many times.  It is probably not
as hard as you are thinking it will be.

Neil


--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS VPS for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select Problem

[Victor Subervi]

On Sun, Dec 6, 2009 at 2:42 PM, Steve Edberg edb...@edberg-online.comwrote:

 At 1:26 PM -0500 12/6/09, Victor Subervi wrote:

 Hi;
 I have the following:

 mysql select * from categoriesProducts as c inner join
 relationshipProducts
 as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent =
 p.ID
 where p.Category = prodCat2;
 ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause'
 mysql describe categoriesProducts;
 +--+-+--+-+-++
 | Field| Type| Null | Key | Default | Extra  |
 +--+-+--+-+-++
 | ID   | int(3) unsigned | NO   | PRI | NULL| auto_increment |
 | Category | varchar(40) | YES  | | NULL||
 | Parent   | varchar(40) | YES  | | NULL||
 +--+-+--+-+-++
 3 rows in set (0.00 sec)

 mysql select * from categoriesProducts;
 ++--++
 | ID | Category | Parent |
 ++--++
 |  1 | prodCat1 | None   |
 |  2 | prodCat2 | None   |
 ++--++
 2 rows in set (0.00 sec)

 So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand
 how
 I specified that in my query. Please advise.
 TIA,
 Victor



 You didn't quote prodCat2 in the query, so it was assuming you were
 referring to the column name. Try:


 select * from categoriesProducts as c inner join relationshipProducts
 as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent =
 p.ID
 where p.Category = 'prodCat2';


Thanks!
V

Re: Select Problem

[Steve Edberg]

At 1:26 PM -0500 12/6/09, Victor Subervi wrote:

Hi;
I have the following:

mysql select * from categoriesProducts as c inner join relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID
where p.Category = prodCat2;
ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause'
mysql describe categoriesProducts;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra  |
+--+-+--+-+-++
| ID   | int(3) unsigned | NO   | PRI | NULL| auto_increment |
| Category | varchar(40) | YES  | | NULL||
| Parent   | varchar(40) | YES  | | NULL||
+--+-+--+-+-++
3 rows in set (0.00 sec)

mysql select * from categoriesProducts;
++--++
| ID | Category | Parent |
++--++
|  1 | prodCat1 | None   |
|  2 | prodCat2 | None   |
++--++
2 rows in set (0.00 sec)

So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how
I specified that in my query. Please advise.
TIA,
Victor



You didn't quote prodCat2 in the query, so it was assuming you were 
referring to the column name. Try:


select * from categoriesProducts as c inner join relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID
where p.Category = 'prodCat2';

- s

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.com/ |
++

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Select through characters

[misiaQ]

Hi,

If the efficiency is the key factor I would suggest to create a trigger on
insert and update and mark the rows in a separate column instead of
executing some fancy string checks during select.

Regards,
m.

-Original Message-
From: nikos [mailto:ni...@qbit.gr] 
Sent: 19 November 2009 08:41
To: mysql@lists.mysql.com
Subject: Select through characters

Hello list
I have a list of names with english and greek characters.
How can select them separately?
I mean, only greeks or only english.

thank you,
Nikos


--
Wst±p do Klubu Gracza, wygraj telefon!
Sprawdz  http://link.interia.pl/f243a


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select through characters [SOLVED]

[nikos]

I find a solution that works:

SELECT writer_id, writer FROM writer WHERE writer REGEXP '^[A-Z]+' ORDER
BY writer

Thank you all
Nikos

misiaQ wrote:
 Hi,

 If the efficiency is the key factor I would suggest to create a trigger on
 insert and update and mark the rows in a separate column instead of
 executing some fancy string checks during select.

 Regards,
 m.

 -Original Message-
 From: nikos [mailto:ni...@qbit.gr] 
 Sent: 19 November 2009 08:41
 To: mysql@lists.mysql.com
 Subject: Select through characters

 Hello list
 I have a list of names with english and greek characters.
 How can select them separately?
 I mean, only greeks or only english.

 thank you,
 Nikos


 --
 Wst?p do Klubu Gracza, wygraj telefon!
 Sprawdz  http://link.interia.pl/f243a


   

Re: SELECT by day

[Jo�o C�ndido de Souza Neto]

Have you tried this?

SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;

Kerstin Finke kerstinfi...@hotmail.com escreveu na mensagem 
news:20090929130406.9802.qm...@lists.mysql.com...
 Hi,
 I want to search all rows with datum = 'tuesday' for example, something 
 like:

 SELECT * FROM `table_anw` WHERE datum=DAYOFWEEK(3);
 '3' as tuesday.

 I found in the manual I can do:
 SELECT DAYOFWEEK('1998-02-03');
 and will get '3' for Tuesday.

 But thats not what I want. I want to select only tuesdays.
 Any ideas?

 Thanks in advance
 Kerstin 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT by day

[Scott Swaim]

Try this

SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;

Scott Swaim
I.T. Director
Total Care / Joshua Family Medical Care
(817) 297-4455 
Website: www.totalcareclinic.com
 
 
NOTICE: The information contained in this e-mail is privileged and
confidential and is intended for the exclusive use of the recipient(s) named
above. If you are not the intended recipient or his or her agent, you are
hereby notified that you have received this document in error and that any
use, disclosure, dissemination, distribution, or copying of this message is
prohibited. If you have received this communication in error, please notify
the sender immediately by e-mail, and delete the original message

-Original Message-
From: Kerstin Finke [mailto:kerstinfi...@hotmail.com] 
Sent: Tuesday, September 29, 2009 8:04 AM
To: mysql@lists.mysql.com
Subject: SELECT by day

Hi,
I want to search all rows with datum = 'tuesday' for example, something 
like:

SELECT * FROM `table_anw` WHERE datum=DAYOFWEEK(3);
'3' as tuesday.

I found in the manual I can do:
SELECT DAYOFWEEK('1998-02-03');
and will get '3' for Tuesday.

But thats not what I want. I want to select only tuesdays.
Any ideas?

Thanks in advance
Kerstin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=sc...@tmcclinic.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT by day

[Rolando Edwards]

SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
SELECT * FROM `table_anw` WHERE WEEKDAY(datum) = 2; 
SELECT * FROM `table_anw` WHERE DAYNAME(datum) = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%W') = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%a') = 'Tue';

DAYOFWEEK function begins back (1 for Sunday, 2 for Monday, ..., 7 for 
Saturday) (See 
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_dayofweek)

WEEKDAY function begins back (0 for Sunday, 1 for Monday, ..., 6 for Saturday) 
(See 
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_weekday)

DAYNAME brings back day of week fully spelled out 
(Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday) (See 
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_dayname)

DATE_FORMAT using special metacharacters for date and time components (see 
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format)

These queries should all bring back the same results where datum is a Tuesday

Give it a try !!!


Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM  Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards

-Original Message-
From: Kerstin Finke [mailto:kerstinfi...@hotmail.com] 
Sent: Tuesday, September 29, 2009 9:04 AM
To: mysql@lists.mysql.com
Subject: SELECT by day

Hi,
I want to search all rows with datum = 'tuesday' for example, something 
like:

SELECT * FROM `table_anw` WHERE datum=DAYOFWEEK(3);
'3' as tuesday.

I found in the manual I can do:
SELECT DAYOFWEEK('1998-02-03');
and will get '3' for Tuesday.

But thats not what I want. I want to select only tuesdays.
Any ideas?

Thanks in advance
Kerstin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select clause using from and to (like rownum in Oracle)

[Peter Brawley]

Is there anyway the SELECT query can be forced to use the from and to
rownum parameters?


1st LIMIT arg = OracleFromArg
2nd LIMIT arg = OracleToArg - OracleFromArg + 1

so 'from 11 to 20' becomes LIMIT 11,10.

PB

-

Anoop kumar V wrote:

Hi All,

I am facing a problem in porting an application written for oracle to run on
mysql.

The application uses a sqlmap (ibatis) at the heart of which is basically a
file that defines all sql's used in the application. It is very well
organized this way. The application uses Oracle as the database. The problem
is that for pagination purposes the sql's written use rownum and accept 2
arguments - the from rownum and the to rownum.

I am trying to run the same application on my laptop that runs mysql. I have
migrated all data and all the sql queries work perfectly except the one that
use pagination and the rownum.

I know in mysql there is support for sql using the LIMIT clause, but the
LIMIT seems to take 2 arguments, the first one being the start rownum and
the second being the number of rows to output. I need the second to be the
to rownum. I have done a lot of googling, but apart from just putting a
rownum for the sql output there was no real usages for pagination purposes.

I cannot use the LIMIT as it is in mysql, because that would mean I would
have to change the application logic which I do not want to do. I also do
not want to install Oracle on my laptop, just too heavy.

I have found this to work except I am not sure how to pass a where clause
for the rownum part:

SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t
I was trying something like:

SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t where r.rownum between 10, 20;
or even
SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t where r.rownum=1;

I get the error:
ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause'

Is there anyway the SELECT query can be forced to use the from and to
rownum parameters?

Thanks a lot for any help,
Anoop

  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.409 / Virus Database: 270.13.63/2317 - Release Date: 08/21/09 06:04:00


  

Re: SELECT query question

[Jo�o C�ndido de Souza Neto]

select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = anything or
T2.Source2_Name = anything or
T3.Source3_Name = anything

Not tested.

-- 
João Cândido de Souza Neto
SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS
Fone: (0XX41) 3033-3636 - JS
www.siens.com.br

Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem 
news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com...
Hello.

I have 4 tables:

MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)

And a search box.

A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get Main_ID

How to make it?

Thanks, Inna




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT query question

[Gavin Towey]

Should be more efficient to do something like:

SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name'
UNION
SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name'
UNION
SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name'


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, July 27, 2009 1:09 PM
To: mysql@lists.mysql.com
Subject: Re: SELECT query question

select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = anything or
T2.Source2_Name = anything or
T3.Source3_Name = anything

Not tested.

--
João Cândido de Souza Neto
SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS
Fone: (0XX41) 3033-3636 - JS
www.siens.com.br

Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem
news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com...
Hello.

I have 4 tables:

MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)

And a search box.

A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get Main_ID

How to make it?

Thanks, Inna




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT query question

[Jo�o C�ndido de Souza Neto]

There are many ways to get the same result. hehehehe

Gavin Towey gto...@ffn.com escreveu na mensagem 
news:30b3df511cec5c4dae4d0d290504753413956dc...@aaa.pmgi.local...
Should be more efficient to do something like:

SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name'
UNION
SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name'
UNION
SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name'


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, July 27, 2009 1:09 PM
To: mysql@lists.mysql.com
Subject: Re: SELECT query question

select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = anything or
T2.Source2_Name = anything or
T3.Source3_Name = anything

Not tested.

Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem
news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com...
Hello.

I have 4 tables:

MainTable (Main_ID, Main_Name)
Table1 (Source1_ID, Source1_Name, Main_ID)
Table2 (Source2_ID, Source2_Name, Main_ID)
Table3 (Source3_ID, Source3_Name, Main_ID)

And a search box.

A user can type any names from Source1_Name or Source2_Name or
Source3_Name.
I need to get Main_ID

How to make it?

Thanks, Inna




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the 
original message. 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Select Into OUTFILE problem

[Gavin Towey]

Hi Bruce,



SELECT ... INTO OUTFILE always creates the file local to the database server.  
If you want to dump results where your perl script is running you'll have to 
use another method such as receiving the results of the query normally and 
writing the file in the perl script.



Regards,

Gavin Towey





I have a bit of perl code that ends with an error:



$sql=SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status,

 a.reason, a.tl

  INTO OUTFILE  '/application/result.csv'

  FIELDS TERMINATED BY ','

  ENCLOSED BY '\'

  LINES TERMINATED BY '\n'

  FROM alerts a

  WHERE a.stamp BETWEEN ? AND ?

  ORDER BY a.stamp DESC;



 $sth = $dbh-prepare($sql);

 $rv = $sth-execute;



DBD::mysql::st execute failed: Can't create/write to file

'/application/result.csv' (Errcode: 2)



Te database is remote from the system where the perl is executing.



Te SQL works as expected when fed to mysql command line client

(i.e. mysql -h remote).  The outfile ends up in the application

directory of the macine running the mysql client.



What I'd found is, when the perl code runs the file tries to drop on the

database server and the application directory doesn't exist there giving

me the error.



Any suggestions to get the outfile to drop in the right place would be

appreciated.



Bruce





The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

Re: Select Into OUTFILE problem

[Bruce Ferrell]

Thanks all who replied.

After I posted I kept looking and found it... Also had folks point it
out to me.

Your suggestion is what I ended up doing.

Bruce



Gavin Towey wrote:
 Hi Bruce,
 
  
 
 SELECT … INTO OUTFILE always creates the file local to the database
 server.  If you want to dump results where your perl script is running
 you’ll have to use another method such as receiving the results of the
 query normally and writing the file in the perl script.
 
  
 
 Regards,
 
 Gavin Towey
 
  
 
  
 
 I have a bit of perl code that ends with an error:
 
  
 
 $sql=SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status,
 
  a.reason, a.tl
 
   INTO OUTFILE  '/application/result.csv'
 
   FIELDS TERMINATED BY ','
 
   ENCLOSED BY '\'
 
   LINES TERMINATED BY '\n'
 
   FROM alerts a
 
   WHERE a.stamp BETWEEN ? AND ?
 
   ORDER BY a.stamp DESC;
 
  
 
  $sth = $dbh-prepare($sql);
 
  $rv = $sth-execute;
 
  
 
 DBD::mysql::st execute failed: Can't create/write to file
 
 '/application/result.csv' (Errcode: 2)
 
  
 
 Te database is remote from the system where the perl is executing.
 
  
 
 Te SQL works as expected when fed to mysql command line client
 
 (i.e. mysql -h remote).  The outfile ends up in the application
 
 directory of the macine running the mysql client.
 
  
 
 What I'd found is, when the perl code runs the file tries to drop on the
 
 database server and the application directory doesn't exist there giving
 
 me the error.
 
  
 
 Any suggestions to get the outfile to drop in the right place would be
 
 appreciated.
 
  
 
 Bruce
 
  
 
  
 
 
 
 The information contained in this transmission may contain privileged
 and confidential information. It is intended only for the use of the
 person(s) named above. If you are not the intended recipient, you are
 hereby notified that any review, dissemination, distribution or
 duplication of this communication is strictly prohibited. If you are not
 the intended recipient, please contact the sender by reply email and
 destroy all copies of the original message.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT of records that have a matching record in a many to many table

[Martijn Engler]

If I understand your question correctly (and I'm really not sure about
that), you're using a LEFT JOIN where you actually want to use a RIGHT
JOIN.
Or you need to rewrite your query and set the tables in another order.

On Thu, Apr 30, 2009 at 23:01, Nigel Peck nigel.p...@miswebdesign.com wrote:

 Can someone please help me with this one?

 I'm trying to SELECT from a table only those records that have a record,
 matching a search term, in a table related by a many to many relationship.
 The many to many relationship is in a mapping/junction table.

 Here's an example of what I have so far:

 -=-=-=-=-=-=-=-=-=-=-=-=-

 SELECT
 `Notes`.`note_id`
 FROM
 `Notes`

 INNER JOIN
 `Notes__Districts`
 ON
 `Notes__Districts`.`note_id` = `Notes`.`note_id`

 LEFT JOIN
 `Districts`
 ON
 `Districts`.`district_id` = `Notes__Districts`.`district_id`

 WHERE
 `Districts`.`name` REGEXP 'bradford';

 -=-=-=-=-=-=-=-=-=-=-=-=-

 Hopefully someone can see what I'm trying to do here and point me in the
 right direction :)

 Maybe I need to use a subquery? I've got a feeling I can do this without
 that but can't get my head round how to set up the JOINs in this case with
 having to use three tables in the one query, I'm only used to two tables at
 once.

 I couldn't find any tutorials that cover this.

 Thanks in advance,
 Nigel


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
  http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: select data from two table and will sort by price in second table

[abdulazeez alugo]

 From: haidarpes...@gmail.com
 To: mysql@lists.mysql.com
 Subject: select data from two table and will sort by price in second table
 Date: Wed, 29 Apr 2009 10:46:48 +0700
 
 dear all,
 please help us mien for select data from two table with details as follows: 
 
 primery tabel : bookcatalog
 second table : pricelist
 
 for seaching we will try to sort by price (in second table).
 
 our databese details like this:
 
 SELECT id, title, author from bookcatalog where isbn LIKE '%$query%' or title 
 LIKE '%$query%' or author LIKE '%$query%'
 
 for information id in bookcatalog and pricelist are same.
 
 thanks a lot
 
 Haidarpesebe

 

Hi,

Can you be clearer please?


_
Show them the way! Add maps and directions to your party invites. 
http://www.microsoft.com/windows/windowslive/products/events.aspx

Re: Select field with multiple values using LIKE

[Johan De Meersman]

AFAIK, repeated LIKEs.

On Tue, Mar 24, 2009 at 6:24 AM, Yariv Omer yar...@jungo.com wrote:

 Hi

 when I am using a query for several field's values I am using the following
 query:
 Select field from table where in ('11', '22')

 I need to do a LIKE search (not exact match but like match)

 How can I do it

 Thanks, Yariv



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be




-- 
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

Re: Select query locks tables in Innodb

[Perrin Harkins]

2009/3/12 Carl c...@etrak-plus.com:
 I am still a little puzzled about how we could have a relatively large set
 of records (100,000+) and yet not cause any table to be locked as the server
 has only 8GB of memory.

What's the relationship you're implying between memory and locking?
Multi-version concurrency doesn't necessarily mean the older versions
that are being read from have to be entirely in memory.

 InnoDB will lock on a query that doesn't use an index.

It shouldn't lock on a SELECT query, regardless of the indexes involved.

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Carl]

Brent,

After a delay while I was busy killing alligators, I did as you suggested 
(added a composite index of date and organization_serial on 
journal_entry_master... in the spirit of your suggestion, anyway.)  The 
results were interesting:


1.  In my test environment, I could not force a locked file even though I 
opened the dates up to cover 2+ years and changed to an organization that 
had more records.  The 'Explain' is attached as temp1.txt.  You will note 
that it starts with 100,000+ records while the eventual set of records for 
the report is 60,000 because the 100,000+ number includes some journmal 
entries for refund/void/etc. transactions which we have no interest in.


2.  I tried various combinations of indexes but couldn't seem to get any 
better than the composite one on the journal_entry_master.  I did not check 
whether the other options would produce locked files.


I am now going to put this into production and see if it will actually fly.

I am still a little puzzled about how we could have a relatively large set 
of records (100,000+) and yet not cause any table to be locked as the server 
has only 8GB of memory.


Thanks for all your help and Baron's suggestions also.

Carl





- Original Message - 
From: Brent Baisley brentt...@gmail.com

To: Carl c...@etrak-plus.com
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb


Ok, so you have 687 unique organization serial numbers. That's not
very unique, on average it will only narrow down the table to 1/687 of
it's full size. This is probably the source of your locking problem
and where you want to focus.
InnoDB will lock on a query that doesn't use an index. It would have
to lock every record anyway, so why not lock the table?
36,000 records still may be too large of a result set to do record
versioning. But, optimizing your query is the only way to go.

Your date_effective is a lot more granular, so you may want to focus
on that. If you do a lot of these types of searches, you can try
creating a compound index on organization_serial+date_effective.
CREATE INDEX (org_date) ON
journal_entry_master(organization_serial,date_effective)

MySQL would/should then use that query, which will narrow things down
quicker and better. It shouldn't have to try to do versioning on
56,000 records while it tries to get the subset of that (36,000).

Brent

On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote:

Brent,

The query returns about 36,000 rows. The 56,000 rows from the
journal_entry_master table is all the entries for organization 16 (they 
span

more than the dates I have asked for.)

SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary 
index

(auto-increment), 687 for the organization_serial (the one I am using), 18
for the organization_shift_start (I tried this before, i.e., starting with
the organization_shift, but it quickly got mired down) and 777,000+ for 
the

date_effective.

If I understand correctly, you have suggested using the date index. The
difficulty is the data contains many organizations and so the date range
query returns 163,000+ rows.

Also, I would expect scaling a query where I had to programatically cut it
up would 1) be difficult and 2) wouldn't really solve the problem but 
would
rather just shorten the time of the locks. I am not suggesting that I 
might

not end up there, only hoping for a better solution.

Thanks for all your insight and feel free to suggest away.

Carl

- Original Message - From: Brent Baisley brentt...@gmail.com
To: Carl c...@etrak-plus.com
Sent: Wednesday, March 04, 2009 4:23 PM
Subject: Re: Select query locks tables in Innodb


Is the result of the query returning 56,000+ rows? How many rows are
you expecting to be returned once the query is finished running?
Your date range is over a year. You may actually get much better
performance (and avoid locking) by running more queries with a
narrower date range and linking them through a UNION. It's using the
organization index rather than the date index.
I don't know your dataset, but typically you want your query to use
the date index since that narrows down the data set better.

You can run SHOW INDEX FROM journal_entry_master to see the
distribution of your data in the index. The cardinality column will
indicate the uniqueness of your data. The higher the number, the more
unique values.

Brent

2009/3/4 Carl c...@etrak-plus.com:


Under stress (having transaction entered), the query shows that it is
still
locking the tables. I rewrote the query and tested it step by step but
could not tell whether tyhe partially complete query was locking tables
because it ran so fast. However, when I had all the pieces in the query
(copy attached), I could easily see it was locking tables using the 
Server

Monitor in Navicat.

Explain (copy as text and copy as Excel attached) seems to indicate that
it
is fairly good although the first step does get quite a few rows.

Does anyone

Re: Select query locks tables in Innodb

[Brent Baisley]

The nice thing about InnnoDB is that it won't have to access the data
portion of the file if it doesn't have to. So if all the information
you are retrieving is contained in an index, it only accesses the
index to get the information it needs. The data portion is never
access, and thus never locked.

Something like this is probably going on. All the information it needs
for the 100,000 records is contained in the index, the the data
portion is never accessed until it needs to retrieve the 60,000
records.

That's a simplistic overview of what could be going on. But it sounds
like your issue has been resolved.

Interesting, your temp1 attached file shows mysql switched from using
the org_date index to the organization index.

Brent Baisley

2009/3/12 Carl c...@etrak-plus.com:
 Brent,

 After a delay while I was busy killing alligators, I did as you suggested
 (added a composite index of date and organization_serial on
 journal_entry_master... in the spirit of your suggestion, anyway.)  The
 results were interesting:

 1.  In my test environment, I could not force a locked file even though I
 opened the dates up to cover 2+ years and changed to an organization that
 had more records.  The 'Explain' is attached as temp1.txt.  You will note
 that it starts with 100,000+ records while the eventual set of records for
 the report is 60,000 because the 100,000+ number includes some journmal
 entries for refund/void/etc. transactions which we have no interest in.

 2.  I tried various combinations of indexes but couldn't seem to get any
 better than the composite one on the journal_entry_master.  I did not check
 whether the other options would produce locked files.

 I am now going to put this into production and see if it will actually fly.

 I am still a little puzzled about how we could have a relatively large set
 of records (100,000+) and yet not cause any table to be locked as the server
 has only 8GB of memory.

 Thanks for all your help and Baron's suggestions also.

 Carl





 - Original Message - From: Brent Baisley brentt...@gmail.com
 To: Carl c...@etrak-plus.com
 Sent: Thursday, March 05, 2009 1:12 PM
 Subject: Re: Select query locks tables in Innodb


 Ok, so you have 687 unique organization serial numbers. That's not
 very unique, on average it will only narrow down the table to 1/687 of
 it's full size. This is probably the source of your locking problem
 and where you want to focus.
 InnoDB will lock on a query that doesn't use an index. It would have
 to lock every record anyway, so why not lock the table?
 36,000 records still may be too large of a result set to do record
 versioning. But, optimizing your query is the only way to go.

 Your date_effective is a lot more granular, so you may want to focus
 on that. If you do a lot of these types of searches, you can try
 creating a compound index on organization_serial+date_effective.
 CREATE INDEX (org_date) ON
 journal_entry_master(organization_serial,date_effective)

 MySQL would/should then use that query, which will narrow things down
 quicker and better. It shouldn't have to try to do versioning on
 56,000 records while it tries to get the subset of that (36,000).

 Brent

 On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote:

 Brent,

 The query returns about 36,000 rows. The 56,000 rows from the
 journal_entry_master table is all the entries for organization 16 (they
 span
 more than the dates I have asked for.)

 SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary
 index
 (auto-increment), 687 for the organization_serial (the one I am using), 18
 for the organization_shift_start (I tried this before, i.e., starting with
 the organization_shift, but it quickly got mired down) and 777,000+ for
 the
 date_effective.

 If I understand correctly, you have suggested using the date index. The
 difficulty is the data contains many organizations and so the date range
 query returns 163,000+ rows.

 Also, I would expect scaling a query where I had to programatically cut it
 up would 1) be difficult and 2) wouldn't really solve the problem but
 would
 rather just shorten the time of the locks. I am not suggesting that I
 might
 not end up there, only hoping for a better solution.

 Thanks for all your insight and feel free to suggest away.

 Carl

 - Original Message - From: Brent Baisley brentt...@gmail.com
 To: Carl c...@etrak-plus.com
 Sent: Wednesday, March 04, 2009 4:23 PM
 Subject: Re: Select query locks tables in Innodb


 Is the result of the query returning 56,000+ rows? How many rows are
 you expecting to be returned once the query is finished running?
 Your date range is over a year. You may actually get much better
 performance (and avoid locking) by running more queries with a
 narrower date range and linking them through a UNION. It's using the
 organization index rather than the date index.
 I don't know your dataset, but typically you want your query to use
 the date index since

Re: Select query locks tables in Innodb

[Carl]

I really appreciate the time you have taken to help me with this problem.

I will be out of the office until around 1:00PM and will try your 
suggestions.


I did attach a copy of the query but it may have been stripped somewhere 
along the line so I have placed it in line below.


select *
from payment_to_fee_link_budget_account_detail_link, journal_entry_master, 
journal_entry_type,
 payment_to_fee_link_event, payment_to_fee_link, fees, fees_event, 
fees_budget_account_detail_link, person, transactions
  left join regs on regs.transactions_serial = 
transactions.transactions_serial,
 transaction_event, receipt_master, budget_account_detail, 
budget_account_detail as ptfl_budget_account_detail, budget_account_master

where journal_entry_master.organization_serial = 16
 and journal_entry_master.date_effective = '2008-01-01'
 and journal_entry_master.date_effective  '2009-03-31'
 and journal_entry_type.journal_entry_type_serial = 
journal_entry_master.journal_entry_type_serial
 and 
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial = 
journal_entry_master.journal_entry_master_serial
 and payment_to_fee_link_budget_account_detail_link.date_effective = 
'2008-01-01'
 and payment_to_fee_link_budget_account_detail_link.date_effective  
'2009-03-31'
 and payment_to_fee_link_event.payment_to_fee_link_event_serial = 
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
 and payment_to_fee_link.payment_to_fee_link_serial = 
payment_to_fee_link_event.payment_to_fee_link_serial
 and transaction_event.transaction_event_serial = 
payment_to_fee_link_event.transaction_event_serial

 and fees.fees_serial = payment_to_fee_link.fees_serial
 and transactions.transactions_serial = fees.transactions_serial
 and person.person_serial = transactions.person_serial
 and receipt_master.receipt_serial = transaction_event.receipt_serial
 and fees_event.fees_serial = payment_to_fee_link.fees_serial
 and ( fees_event.transaction_event_description_serial = 13 or 
fees_event.transaction_event_description_serial = 2 )
 and fees_budget_account_detail_link.fees_event_serial = 
fees_event.fees_event_serial
 and budget_account_detail.budget_account_detail_serial = 
fees_budget_account_detail_link.budget_account_detail_serial
 and ptfl_budget_account_detail.budget_account_detail_serial = 
payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
 and budget_account_master.budget_account_serial = 
budget_account_detail.budget_account_serial

 and budget_account_master.budget_account_type_serial = 5001

TIA,

Carl

- Original Message - 
From: Baron Schwartz ba...@xaprb.com

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb


I don't think it locks the tables.  The behavior may be similar, but I
seriously doubt that's what's happening.  Take a snapshot of SHOW
INNODB STATUS while this is going on.  And use mysqladmin debug and
check the error log.  Then put those in some pastebin and send us the
link.  And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running.  There's not a lot I can do
to really help you with this other than assume that you are wrong :)

Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.

5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release.  You might be surprised at how
much that changes things.

Baron

On Wed, Mar 4, 2009 at 3:33 PM, Carl c...@etrak-plus.com wrote:

Baron,

I am using 5.0.37.

While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten 
out

or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)

Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?

TIA,

Carl


- Original Message - From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb



Carl,

Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you

Re: Select query locks tables in Innodb

[Carl]

Thanks to all of you.

The key was the 107488 rows.  I restructured the query so that it started 
with something smaller and it 1) runs faster (I'm guessing the reduced use 
of temp space) and 2) did not seem to cause any locking problems (I will 
test this under load today.)


I have attached a copy of the query which has been simplified in a couple of 
ways (I don't really want every field from every row selected from every 
table.)  Also, the constants like organization_serial (16) and dates are 
variables in the real version.


The explain now shows:

idtable typepossible_keys 
keylenref  rows
1organization_shiftrefPRIMARY, organizationorganization 
4const5
1organization_shift_start ref   PRIMARY, organization_shift 
organization_shift4 organization_shift_serial295
1journal_entry_masterrefPRIMARY, organization_shift_start 
organization_shift_start 5 organization_shift_start_serial 52


Note that it now starts with 5 row, expands to 295 rows, etc. not the 
100,000+ from before.


Again, thanks for all your help.

Carl


- Original Message - 
From: Baron Schwartz ba...@xaprb.com

To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb


On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com 
wrote:

A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just locking the table.


InnoDB does not do lock escalation a la SQL Server etc.  I'd look at
Perrin's suggestions, I think they are likely to be the problem.

More importantly, what is the query? :-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com



# sales from collections
(select *
	from organization_shift, organization_shift_start, transaction_event,payment_to_fee_link_event,payment_to_fee_link, payment_to_fee_link_budget_account_detail_link,fees_budget_account_detail_link, fees_event, budget_account_detail, 
			payments, budget_account_detail as ptfl_budget_account_detail, budget_account_master, journal_entry_master, journal_entry_type, receipt_master, person, transactions

left join regs on regs.transactions_serial = 
transactions.transactions_serial
where organization_shift.organization_serial = 16
and organization_shift_start.organization_shift_serial = 
organization_shift.organization_shift_serial
and organization_shift_start.date_effective = '2008-01-01'
and organization_shift_start.date_effective  '2009-03-31'
#$P!{organizationShiftStartQuery}
and journal_entry_master.organization_shift_start_serial = 
organization_shift_start.organization_shift_start_serial
and receipt_master.receipt_serial = 
transaction_event.receipt_serial
and transactions.transactions_serial = 
transaction_event.transactions_serial
		and transactions.organization_serial = organization_shift.organization_serial 
		#$P!{itemSerials}

and person.person_serial = transactions.person_serial
and payment_to_fee_link_event.transaction_event_serial = 
transaction_event.transaction_event_serial
and 
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial 
= payment_to_fee_link_event.payment_to_fee_link_event_serial
and 
payment_to_fee_link_budget_account_detail_link.cash_basis_reporting_flag = 'Y'
and payment_to_fee_link.payment_to_fee_link_serial = 
payment_to_fee_link_event.payment_to_fee_link_serial
and payments.payments_serial = 
payment_to_fee_link.payments_serial
and payment_to_fee_link_budget_account_detail_link.date_effective 
= '2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective 
 '2009-03-31'
and (payments.payment_type_code_serial in ( 
1,2,3,4,5,8,24,6,7,12,13,23,25 )# 1,2,3,4,5,8,24,6,7,12,13,23,25
or 
payment_to_fee_link_budget_account_detail_link.description='Apply available 
credit to customer accounts receivable')
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or 
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial = 
fees_event.fees_event_serial
and 
fees_budget_account_detail_link.budget_account_detail_serial

Re: Select query locks tables in Innodb

[Carl]

Under stress (having transaction entered), the query shows that it is still 
locking the tables.  I rewrote the query and tested it step by step but 
could not tell whether tyhe partially complete query was locking tables 
because it ran so fast.  However, when I had all the pieces in the query 
(copy attached), I could easily see it was locking tables using the Server 
Monitor in Navicat.


Explain (copy as text and copy as Excel attached) seems to indicate that it 
is fairly good although the first step does get quite a few rows.


Does anyone have any ideas?

TIA,

Carl



- Original Message - 
From: Baron Schwartz ba...@xaprb.com

To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb


On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com 
wrote:

A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just locking the table.


InnoDB does not do lock escalation a la SQL Server etc.  I'd look at
Perrin's suggestions, I think they are likely to be the problem.

More importantly, what is the query? :-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com



select *
from payment_to_fee_link_budget_account_detail_link, 
journal_entry_master, journal_entry_type,
payment_to_fee_link_event, payment_to_fee_link, fees, 
fees_event, fees_budget_account_detail_link, person, transactions
left join regs on regs.transactions_serial = 
transactions.transactions_serial,
transaction_event, receipt_master, budget_account_detail, 
budget_account_detail as ptfl_budget_account_detail, budget_account_master
where journal_entry_master.organization_serial = 16
and journal_entry_master.date_effective = '2008-01-01'
and journal_entry_master.date_effective  '2009-03-31'
and journal_entry_type.journal_entry_type_serial = 
journal_entry_master.journal_entry_type_serial
and 
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial = 
journal_entry_master.journal_entry_master_serial
and payment_to_fee_link_budget_account_detail_link.date_effective 
= '2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective 
 '2009-03-31'
and payment_to_fee_link_event.payment_to_fee_link_event_serial 
= 
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
and payment_to_fee_link.payment_to_fee_link_serial = 
payment_to_fee_link_event.payment_to_fee_link_serial
		and transaction_event.transaction_event_serial = payment_to_fee_link_event.transaction_event_serial 
		and fees.fees_serial = payment_to_fee_link.fees_serial

and transactions.transactions_serial = fees.transactions_serial
and person.person_serial = transactions.person_serial
and receipt_master.receipt_serial = 
transaction_event.receipt_serial
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or 
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial = 
fees_event.fees_event_serial
and budget_account_detail.budget_account_detail_serial = 
fees_budget_account_detail_link.budget_account_detail_serial
and ptfl_budget_account_detail.budget_account_detail_serial = 
payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
and budget_account_master.budget_account_serial = 
budget_account_detail.budget_account_serial
and budget_account_master.budget_account_type_serial = 5001



temp.XLS
Description: MS-Excel spreadsheet

1   SIMPLE  journal_entry_masterref 
PRIMARY,organization,journal_entry_type_serial,date_effective   organization
4   const   56926   Using where
1   SIMPLE  journal_entry_type  eq_ref  PRIMARY PRIMARY 4   
PRODUCTION.journal_entry_master.journal_entry_type_serial   1   
1   SIMPLE  payment_to_fee_link_budget_account_detail_link  ref 
journal_entry,budget_account_detail_serial,event,date_effective journal_entry   
4   PRODUCTION.journal_entry_master.journal_entry_master_serial 1   
Using where
1   SIMPLE  ptfl_budget_account_detail  eq_ref  PRIMARY PRIMARY 4   
PRODUCTION.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
  1   
1   SIMPLE

Re: Select query locks tables in Innodb

[Perrin Harkins]

2009/3/4 Carl c...@etrak-plus.com:
 However, when I had all the pieces in the query
 (copy attached), I could easily see it was locking tables using the Server
 Monitor in Navicat.

I don't know what that is, but I think you'd better look at something
closer to the bone, like SHOW INNODB STATUS.

 Explain (copy as text and copy as Excel attached) seems to indicate that it
 is fairly good although the first step does get quite a few rows.

EXPLAIN isn't really relevant to table locking.  InnoDB tables should
never let readers block writers for a simple SELECT.

 Does anyone have any ideas?

Did you check that your tables are InnoDB?  Are you running some kind
of crazy isolation level?

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Carl]

One more note.

Perrin asked if I was using any select... for update.  The answer is no, 
neither in the select query that seems to be locking the tables nor in the 
queries that are processing transactions.


Surprisingly, one of the tables that reports being locked is never accessed 
in the report query.  It is a foreign key on one of the files that is used.


TIA,

Carl


- Original Message - 
From: Baron Schwartz ba...@xaprb.com

To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb


On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com 
wrote:

A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just locking the table.


InnoDB does not do lock escalation a la SQL Server etc.  I'd look at
Perrin's suggestions, I think they are likely to be the problem.

More importantly, what is the query? :-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Carl]

I did check that all tables are Innodb.

I was using the Navicat Server Monitor because I know that when I see the 
monitor reporting a status of locked during an attempted query, that user 
comes to a complete halt until the lock is cleared (usually by the bad query 
finishing.)


I will check the isolation level but I believe it is whatever was set out of 
the box (five years ago.)


Thanks,

Carl

- Original Message - 
From: Perrin Harkins per...@elem.com

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 1:49 PM
Subject: Re: Select query locks tables in Innodb



2009/3/4 Carl c...@etrak-plus.com:

However, when I had all the pieces in the query
(copy attached), I could easily see it was locking tables using the 
Server

Monitor in Navicat.


I don't know what that is, but I think you'd better look at something
closer to the bone, like SHOW INNODB STATUS.

Explain (copy as text and copy as Excel attached) seems to indicate that 
it

is fairly good although the first step does get quite a few rows.


EXPLAIN isn't really relevant to table locking.  InnoDB tables should
never let readers block writers for a simple SELECT.


Does anyone have any ideas?


Did you check that your tables are InnoDB?  Are you running some kind
of crazy isolation level?

- Perrin




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Baron Schwartz]

Carl,

Locked status in SHOW PROCESSLIST and a table being locked are
different.  There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases.  What version of MySQL
are you using?

The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.

Baron

On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:
 I did check that all tables are Innodb.

 I was using the Navicat Server Monitor because I know that when I see the
 monitor reporting a status of locked during an attempted query, that user
 comes to a complete halt until the lock is cleared (usually by the bad query
 finishing.)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Baron Schwartz]

Carl,

Locked status in SHOW PROCESSLIST and a table being locked are
different.  There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases.  What version of MySQL
are you using?

The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.

Baron

On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:
 I did check that all tables are Innodb.

 I was using the Navicat Server Monitor because I know that when I see the
 monitor reporting a status of locked during an attempted query, that user
 comes to a complete halt until the lock is cleared (usually by the bad query
 finishing.)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Carl]

Baron,

I am using 5.0.37.

While it may be true that there is a bug that shows tables as being locked 
when they really aren't, I do not think that applies here.  I do know that 
when a table shows a status of 'Locked' in the Navicat Server Monitor that 
the transaction which created and is processing the query comes to a 
complete stop until the report query (the one I am trying to straighten out 
or understand) is finished.  For example, the report query is reading from 
several files, e.g., receipt_master, if a user tries to check out (which 
requires an insert into the receipt_master table), they are stopped until 
the report query finishes and query on that table shows in Navicat as 
waiting for lock ('Locked'.)


Since the report query is only reading data, I am puzzled why it locks the 
tables.  Any ideas?


TIA,

Carl


- Original Message - 
From: Baron Schwartz ba...@xaprb.com

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb



Carl,

Locked status in SHOW PROCESSLIST and a table being locked are
different.  There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases.  What version of MySQL
are you using?

The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.

Baron

On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:

I did check that all tables are Innodb.

I was using the Navicat Server Monitor because I know that when I see the
monitor reporting a status of locked during an attempted query, that user
comes to a complete halt until the lock is cleared (usually by the bad 
query

finishing.)





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Baron Schwartz]

I don't think it locks the tables.  The behavior may be similar, but I
seriously doubt that's what's happening.  Take a snapshot of SHOW
INNODB STATUS while this is going on.  And use mysqladmin debug and
check the error log.  Then put those in some pastebin and send us the
link.  And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running.  There's not a lot I can do
to really help you with this other than assume that you are wrong :)

Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.

5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release.  You might be surprised at how
much that changes things.

Baron

On Wed, Mar 4, 2009 at 3:33 PM, Carl c...@etrak-plus.com wrote:
 Baron,

 I am using 5.0.37.

 While it may be true that there is a bug that shows tables as being locked
 when they really aren't, I do not think that applies here.  I do know that
 when a table shows a status of 'Locked' in the Navicat Server Monitor that
 the transaction which created and is processing the query comes to a
 complete stop until the report query (the one I am trying to straighten out
 or understand) is finished.  For example, the report query is reading from
 several files, e.g., receipt_master, if a user tries to check out (which
 requires an insert into the receipt_master table), they are stopped until
 the report query finishes and query on that table shows in Navicat as
 waiting for lock ('Locked'.)

 Since the report query is only reading data, I am puzzled why it locks the
 tables.  Any ideas?

 TIA,

 Carl


 - Original Message - From: Baron Schwartz ba...@xaprb.com
 To: Carl c...@etrak-plus.com
 Cc: mysql@lists.mysql.com
 Sent: Wednesday, March 04, 2009 2:29 PM
 Subject: Re: Select query locks tables in Innodb


 Carl,

 Locked status in SHOW PROCESSLIST and a table being locked are
 different.  There is a bug in MySQL that shows Locked status for
 queries accessing InnoDB tables in some cases.  What version of MySQL
 are you using?

 The table is not really locked, you're just seeing that as a side
 effect of whatever's really happening.

 Baron

 On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:

 I did check that all tables are Innodb.

 I was using the Navicat Server Monitor because I know that when I see the
 monitor reporting a status of locked during an attempted query, that user
 comes to a complete halt until the lock is cleared (usually by the bad
 query
 finishing.)






-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Brent Baisley]

A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just locking the table.

Is that how many records you want to return? That seems like a lot.
Maybe reworking your query may help. Heck, post the sizeable query.
You've been spending weeks on it.

Brent Baisley

On Tue, Mar 3, 2009 at 10:53 AM, Carl c...@etrak-plus.com wrote:
 I have been wrestling with this problem for a couple of weeks and have been 
 unable to find a solution.

 The MySQL version is  5.0.37 and it is running on a Slackware Linux 11 box.

 The problem:

 A query that is selecting data for a report locks the files that it accesses 
 forcing users who are attempting to enter transactions to wait until the 
 select query is finished.

 The query is sizable so I have not included it here (I can if that would be 
 helpful.)  Explain shows (abbreviated):

 id   select_type    table                type        possible keys            
                 key_len   ref        rows         Extra
 1    SIMPLE         transactions     ref           
 PRIMARY,person,organization    4            const    107448      *
 1    SIMPLE         person             eq_ref      PRIMARY                    
              4    person_serial    1
 1    SIMPLE         regs                 ref           transaction            
                     4    transactions_serial  1
 1    SIMPLE         transaction_event ref        PRIMARY, transaction, 
 receipt    4    transactions_serial    1
 1    SIMPLE         receipt_master    ref         PRIMARY                     
             4    receipt_serial            1

 The 107448 rows are the transactions for the organization I am reporting.  
 The person is linked directly to the transaction.  During the select query, 
 the person table is locked thereby stopping updates to any person in the 
 table.

 I have always thought a select is only a read and would, therefore, not lock 
 any tables.

 Anyone have any ideas?

 TIA,

 Carl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Perrin Harkins]

On Tue, Mar 3, 2009 at 10:53 AM, Carl c...@etrak-plus.com wrote:
 A query that is selecting data for a report locks the files that it accesses 
 forcing users who are attempting to enter transactions to wait until the 
 select query is finished.

Is it an INSERT INTO...SELECT FROM?  Those lock.  Also, have you
verified that each table you think is InnoDB really is?  Do a SHOW
CREATE TABLE on them.

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select query locks tables in Innodb

[Baron Schwartz]

On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote:
 A SELECT will/can lock a table. It almost always does in MyISAM (no
 insert/updates), almost never does in InnoDB. There is an exception to
 every rule. The problem is most likely in the 107488 rows part of the
 query. That's too many rows for InnoDB to keep a version history on so
 it's likely just locking the table.

InnoDB does not do lock escalation a la SQL Server etc.  I'd look at
Perrin's suggestions, I think they are likely to be the problem.

More importantly, what is the query? :-)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Select data from another Mysql Server?

[Baron Schwartz]

Hello,

On Thu, Feb 12, 2009 at 11:54 PM, Kiran Waje kiranw...@gmail.com wrote:
 I have two Mysql servers and I want to Read data from one Mysql server to
 another using stored procedure.


You may be able to use the Federated engine.  Check the output of SHOW ENGINES.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: SELECT with replacement chars

[Weston, Craig (OFT)]

Hi
how can i replace, in a SELECT query, the last 3 numbers with
asterisks?
from 0123456789 to 0123456***


My psudocode for this would be something like:

Select 

CONCAT(left$(`field`,(LENGTH(a)-3),'***')
From `table`


But there might be more efficient ideas out there.

This e-mail, including any attachments, may be confidential, privileged or 
otherwise legally protected. It is intended only for the addressee. If you 
received this e-mail in error or from someone who was not authorized to send it 
to you, do not disseminate, copy or otherwise use this e-mail or its 
attachments.  Please notify the sender immediately by reply e-mail and delete 
the e-mail from your system.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SELECT with replacement chars

[Jerry Schwartz]

-Original Message-
From: spacemarc [mailto:[EMAIL PROTECTED]
Sent: Friday, December 05, 2008 8:50 AM
To: MySQL
Subject: SELECT with replacement chars

Hi
how can i replace, in a SELECT query, the last 3 numbers with asterisks?
from 0123456789 to 0123456***

[JS]
SELECT CONCAT(LEFT(myfield,LENGTH(myfield) - 3), '***') FROM table;

If you are using a multi-byte character set, use CHAR_LENGTH() instead of
LENGTH().




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT with replacement chars

[spacemarc]

thanks, it works fine (on mysql 4 and 5)


 SELECT CONCAT(LEFT(myfield,LENGTH(myfield) - 3), '***') FROM table;



-- 
Scripts: http://www.spacemarc.it

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT locking tables.... in other databases

[Ananda Kumar]

can u please do show full processlist  when the update is happening,  and
if its innodb

please do SHOW INNODB STATUS, which will give complete activity on innodb
engine, including lock information.

Please show use the output of these.

regards
anandkl


On 11/27/08, David Scott [EMAIL PROTECTED] wrote:

 Hi list.We have 2 tables, both have a few inserts, many updates and the
 occasional select.
 When running a select joining the 2 tables (which can take upto 20 seconds
 to complete, they are large tables) all updates are blocked and the
 maxconnections is quickly reached.

 We tried copying the data to a 2nd database (in the same MySQL install) to
 run the select on that but for some reason that still prevents the updates
 on the original database, we watch the connections and we see them build
 up,
 when the select finishes they quickly clear.

 My question is how can we prevent this backing up of updates when running a
 select and why would doing a select on one database cause connections on
 another to back up?

 Thanks
 --
 David

Re: SELECT locking tables.... in other databases

[David Scott]

show full processlist
userX is the user the site is using to connect
databaseX is the database in question

1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', ''
13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', ''
13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', ''
13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', ''
13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full processlist'
13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', ''

Show innodb status:

'
=
081127 11:12:38 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 1 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 164975, signal count 155476
Mutex spin waits 0, rounds 7441650, OS waits 120688
RW-shared spins 37873, OS waits 17328; RW-excl spins 24776, OS waits 4966

TRANSACTIONS

Trx id counter 0 25118320
Purge done for trx's n:o  0 25118124 undo n:o  0 0
History list length 89
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 19166, OS thread id 1096026448
MySQL thread id 13833472, query id 57762790 IPX dave
SHOW INNODB STATUS
---TRANSACTION 0 25063765, not started, process no 19166, OS thread id
1182529872
MySQL thread id 13508974, query id 57762327 IPX  dave
---TRANSACTION 0 0, not started, process no 19166, OS thread id 1176140112
MySQL thread id 13775621, query id 57762659 IPX  sen
---TRANSACTION 0 0, not started, process no 19166, OS thread id 1181997392
MySQL thread id 13759139, query id 57665031 IPX  sen
---TRANSACTION 0 25117242, not started, process no 19166, OS thread id
1188387152
MySQL thread id 1976156, query id 57760643 localhost beta_cc

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
28 OS file reads, 12527564 OS file writes, 12361532 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 79.92 writes/s, 79.92 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH INDEX
-
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 2310107, used cells 513846, node heap has 859 buffer(s)
34.97 hash searches/s, 99.90 non-hash searches/s
---
LOG
---
Log sequence number 1 501773721
Log flushed up to   1 501773721
Last checkpoint at  1 500074343
0 pending log writes, 0 pending chkp writes
12329746 log i/o's done, 79.92 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 1201497898; in additional pool allocated 558592
Buffer pool size   64000
Free buffers   3328
Database pages 59813
Modified db pages  253
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 65, created 59748, written 573841
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--
ROW OPERATIONS
--
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 19166, id 1171347792, state: sleeping
Number of rows inserted 17853779, updated 10095603, deleted 18, read
948444635
13.99 inserts/s, 65.93 updates/s, 0.00 deletes/s, 98.90 reads/s

END OF INNODB MONITOR OUTPUT

'


I hope that helps--
Dave


2008/11/27 Ananda Kumar [EMAIL PROTECTED]

 can u please do show full processlist  when the update is happening,  and
 if its innodb

 please do SHOW INNODB STATUS, which will give complete activity on innodb
 engine, including lock information.

 Please show use the output of these.

 regards
 anandkl


 On 11/27/08, David Scott [EMAIL PROTECTED] wrote:

 Hi list.We have 2 tables, both have a few inserts, many updates and the
 occasional select.
 When running a select joining the 2 tables (which can take upto 20 seconds
 to complete, they are large tables) all updates are blocked and the
 maxconnections is quickly reached.

 We tried copying the data to a 2nd database (in the same MySQL install) to
 run the select on that but for some reason that still prevents the updates
 on the original database, we watch the connections and we see them build
 up,
 when the select finishes they quickly clear.

 My question is how can we prevent this backing up of updates when running
 a
 select and why would doing a select on one database cause connections on
 another to back up?

 Thanks
 --
 David

Re: SELECT locking tables.... in other databases

[Pradeep Chandru]

Hi David,
  can you please let me know what is the select query and the update query
along with the explain plan of the same.
   can you please let me know if you are using innodb storage engine?

Regards,
Chandru
www.mafiree.com

On Thu, Nov 27, 2008 at 4:45 PM, David Scott
[EMAIL PROTECTED]wrote:

 show full processlist
 userX is the user the site is using to connect
 databaseX is the database in question

 1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', ''
 13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', ''
 13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', ''
 13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', ''
 13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full processlist'
 13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', ''

 Show innodb status:

 '
 =
 081127 11:12:38 INNODB MONITOR OUTPUT
 =
 Per second averages calculated from the last 1 seconds
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 164975, signal count 155476
 Mutex spin waits 0, rounds 7441650, OS waits 120688
 RW-shared spins 37873, OS waits 17328; RW-excl spins 24776, OS waits 4966
 
 TRANSACTIONS
 
 Trx id counter 0 25118320
 Purge done for trx's n:o  0 25118124 undo n:o  0 0
 History list length 89
 Total number of lock structs in row lock hash table 0
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1096026448
 MySQL thread id 13833472, query id 57762790 IPX dave
 SHOW INNODB STATUS
 ---TRANSACTION 0 25063765, not started, process no 19166, OS thread id
 1182529872
 MySQL thread id 13508974, query id 57762327 IPX  dave
 ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1176140112
 MySQL thread id 13775621, query id 57762659 IPX  sen
 ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1181997392
 MySQL thread id 13759139, query id 57665031 IPX  sen
 ---TRANSACTION 0 25117242, not started, process no 19166, OS thread id
 1188387152
 MySQL thread id 1976156, query id 57760643 localhost beta_cc
 
 FILE I/O
 
 I/O thread 0 state: waiting for i/o request (insert buffer thread)
 I/O thread 1 state: waiting for i/o request (log thread)
 I/O thread 2 state: waiting for i/o request (read thread)
 I/O thread 3 state: waiting for i/o request (write thread)
 Pending normal aio reads: 0, aio writes: 0,
  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
 Pending flushes (fsync) log: 0; buffer pool: 0
 28 OS file reads, 12527564 OS file writes, 12361532 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 79.92 writes/s, 79.92 fsyncs/s
 -
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -
 Ibuf: size 1, free list len 0, seg size 2,
 0 inserts, 0 merged recs, 0 merges
 Hash table size 2310107, used cells 513846, node heap has 859 buffer(s)
 34.97 hash searches/s, 99.90 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number 1 501773721
 Log flushed up to   1 501773721
 Last checkpoint at  1 500074343
 0 pending log writes, 0 pending chkp writes
 12329746 log i/o's done, 79.92 log i/o's/second
 --
 BUFFER POOL AND MEMORY
 --
 Total memory allocated 1201497898; in additional pool allocated 558592
 Buffer pool size   64000
 Free buffers   3328
 Database pages 59813
 Modified db pages  253
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages read 65, created 59748, written 573841
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 Buffer pool hit rate 1000 / 1000
 --
 ROW OPERATIONS
 --
 0 queries inside InnoDB, 0 queries in queue
 1 read views open inside InnoDB
 Main thread process no. 19166, id 1171347792, state: sleeping
 Number of rows inserted 17853779, updated 10095603, deleted 18, read
 948444635
 13.99 inserts/s, 65.93 updates/s, 0.00 deletes/s, 98.90 reads/s
 
 END OF INNODB MONITOR OUTPUT
 
 '


 I hope that helps--
 Dave


 2008/11/27 Ananda Kumar [EMAIL PROTECTED]

  can u please do show full processlist  when the update is happening,
  and
  if its innodb
 
  please do SHOW INNODB STATUS, which will give complete activity on
 innodb
  engine, including lock information.
 
  Please show use the output of these.
 
  regards
  anandkl
 
 
  On 11/27/08, David Scott [EMAIL PROTECTED] wrote:
 
  Hi list.We have 2 tables, both have a few inserts, many updates and the
  occasional select.
  When running a select joining the 2 tables (which can take upto 20
 seconds
  to complete, they are large tables) all updates are blocked and the
  maxconnections is quickly reached.
 
  We tried copying the data to a 2nd database (in the same MySQL install)
 to
  run the select on that but for some reason that still prevents the
 updates
  on the original database, we watch the 

Re: SELECT locking tables.... in other databases

[David Scott]

Thanks for the improved query.The indexing didn't help much and still the
main problem is it locking all updates to the tables while it executes...
even if I am executing it on a copy of the tables in a different database
--
Dave

2008/11/27 Chandru [EMAIL PROTECTED]

 Hi David,
  please create index on games_sessions_levels table on the column startTime
 and the query can be rewritten as
 gSL.starttime between unix_timestamp('2008-11-26') and
 unix_timestamp('2008-11-26') and gSL.endTime  0

 gSL.startTime  0 is invalid since already the value that you verify is
 between  unix_timestamp('2008-11-26') and  unix_timestamp('2008-11-26')


 gSL.starttime  unix_timestamp('2008-11-26')
 AND gSL.startTime  0 AND gSL.endTime  0
 AND gSL.starttime  unix_timestamp('2008-11-26')

 Regards,
 Chandru
 www.mafiree.com
 On Thu, Nov 27, 2008 at 5:50 PM, David Scott 
 [EMAIL PROTECTED] wrote:

 Ah yes, sorry, here it is:
  SELECT gS.gameid, g.gamename, COUNT(DISTINCT(gS.sessionid)) 'sessions',
 SUM(gSL.endTime - gSL.startTime)/COUNT(DISTINCT(gS.sessionid))/60 'average
 SESSION time',
 SUM(gSL.totalTime)/COUNT(DISTINCT(gS.sessionid))/60 'average PLAY time',
 SUM(gSL.totalTime)/60
 FROM databaseX.games_sessions_levels gSL
 JOIN databaseX.games_sessions gS ON gS.sessionid = gSL.sessionid
 JOIN databaseX.games g ON g.id = gS.gameid
 WHERE gSL.starttime  unix_timestamp('2008-11-26')
 AND gSL.startTime  0 AND gSL.endTime  0
 AND gSL.starttime  unix_timestamp('2008-11-27')
 GROUP BY gS.gameid
 ORDER BY SUM(gSL.totalTime) DESC



 CREATE TABLE  `databaseX `.`Xgames_sessions` (
   `sessionid` int(99) NOT NULL auto_increment,
   `playerid` varchar(32) NOT NULL,
   `gameid` int(99) NOT NULL,
   `starttime` int(20) NOT NULL,
   `zone` varchar(255) NOT NULL,
   `host` varchar(255) NOT NULL,
   `loadref` varchar(50) NOT NULL,
   PRIMARY KEY  (`sessionid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=604907 DEFAULT CHARSET=latin1
 ROW_FORMAT=DYNAMIC;


 CREATE TABLE  `databaseX`.`Xgames_sessions_levels` (
   `id` int(99) NOT NULL auto_increment,
   `sessionid` int(99) NOT NULL,
   `levelnumber` int(99) NOT NULL,
   `levelname` varchar(50) default NULL,
   `starttime` int(20) NOT NULL,
   `endtime` int(20) NOT NULL,
   `totaltime` int(20) NOT NULL default '0',
   `info` int(11) NOT NULL,
   `score` int(99) NOT NULL,
   `done` tinyint(1) NOT NULL default '0',
   `zone` varchar(50) NOT NULL,
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=4213995 DEFAULT CHARSET=latin1
 ROW_FORMAT=DYNAMIC;

   --
 Dave

 2008/11/27 Chandru [EMAIL PROTECTED]

 Hai david,
without seeing the query i cant comment if the index that is created
 is efficient or not. Please send the query if you need more help.

 Regards,
 Chandru
 www.mafiree.com

   On Thu, Nov 27, 2008 at 5:36 PM, David Scott 
 [EMAIL PROTECTED] wrote:

 We only indexed the id on the table as it has many many updates and very
 rarely do we select, we assumed this would be more efficient? --
 Dave

 2008/11/27 Chandru [EMAIL PROTECTED]

  Hi David,
   I find that the query is going for a full table scan. i think u need
 to optimize the query. Can you please send the original query and also the
 output of  show index from tablename;

 Regards,
 Chandru.
 www.mafiree.com

   On Thu, Nov 27, 2008 at 5:18 PM, David Scott 
 [EMAIL PROTECTED] wrote:

 We are using innodb.
 EXPLAIN on the select:
  1, 'SIMPLE', 'gSL', 'ALL', '', '', '', '', 4210688, 'Using where;
 Using temporary; Using filesort'
 1, 'SIMPLE', 'gS', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
 'databaseX.gSL.sessionid', 1, ''
 1, 'SIMPLE', 'g', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
 'databaseX.gS.gameid', 1, ''

 We are baffled by how doing it on one DB can lock updates in another
 --
 Dave


 2008/11/27 Pradeep Chandru [EMAIL PROTECTED]

  Hi David,
   can you please let me know what is the select query and the update
 query along with the explain plan of the same.
can you please let me know if you are using innodb storage engine?

 Regards,
 Chandru
 www.mafiree.com

   On Thu, Nov 27, 2008 at 4:45 PM, David Scott 
 [EMAIL PROTECTED] wrote:

 show full processlist
 userX is the user the site is using to connect
 databaseX is the database in question

 1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', ''
 13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', ''
 13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', ''
 13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', ''
 13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full
 processlist'
 13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', ''

 Show innodb status:

 '
 =
 081127 11:12:38 INNODB MONITOR OUTPUT
 =
 Per second averages calculated from the last 1 seconds
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 164975, signal count 155476
 Mutex spin waits 0, rounds 7441650, OS waits 120688
 RW-shared spins 37873, OS waits 17328; RW-excl spins 

Re: SELECT through many databases

[Peter Brawley]

Andre Matos wrote:

Today I have 5, but tomorrow I can have 50 and I don't want to forget 
any database.


Do it in an app language or as a PREPARED statement in an sproc.

PB

---

Andre Matos wrote:

Hello,

Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They all 
have the same structure but different data.


I would like perform this select

SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL

and collect the data from all 5 database. However, I would like to 
avoid doing something like this:


SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL


Today I have 5, but tomorrow I can have 50 and I don't want to forget 
any database.


Thanks for any help.

Andre








Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com 
Version: 8.0.175 / Virus Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM


  

Re: SELECT through many databases

[Brent Baisley]

On Fri, Nov 21, 2008 at 12:44 PM, Andre Matos [EMAIL PROTECTED] wrote:
 Hello,

 Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They all have
 the same structure but different data.

 I would like perform this select

 SELECT TaskDoneOn, TaskDoneBy
 FROM {database}
 WHERE TaskDoneOn IS NOT NULL

 and collect the data from all 5 database. However, I would like to avoid
 doing something like this:

 SELECT TaskDoneOn, TaskDoneBy
 FROM db1
 WHERE TaskDoneOn IS NOT NULL
 UNION
 SELECT TaskDoneOn, TaskDoneBy
 FROM db2
 WHERE TaskDoneOn IS NOT NULL
 UNION
 SELECT TaskDoneOn, TaskDoneBy
 FROM db3
 WHERE TaskDoneOn IS NOT NULL
 UNION
 SELECT TaskDoneOn, TaskDoneBy
 FROM db4
 WHERE TaskDoneOn IS NOT NULL
 UNION
 SELECT TaskDoneOn, TaskDoneBy
 FROM db5
 WHERE TaskDoneOn IS NOT NULL


 Today I have 5, but tomorrow I can have 50 and I don't want to forget any
 database.

 Thanks for any help.

 Andre

Create a MERGE table that is all those tables combined. Then you just
need to do 1 select as if it was one table. Just be sure to update the
MERGE table description when ever you add a table.

Brent Baisley

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT through many databases

[Andre Matos]

I was trying to avoid both since the SELECT statement is not fixed.  
Time to time, users want different information.


Thanks,

Andre

On 21-Nov-08, at 12:59 PM, Peter Brawley wrote:


Andre Matos wrote:

Today I have 5, but tomorrow I can have 50 and I don't want to  
forget any database.


Do it in an app language or as a PREPARED statement in an sproc.

PB

---

Andre Matos wrote:

Hello,

Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They  
all have the same structure but different data.


I would like perform this select

SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL

and collect the data from all 5 database. However, I would like to  
avoid doing something like this:


SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL


Today I have 5, but tomorrow I can have 50 and I don't want to  
forget any database.


Thanks for any help.

Andre








Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus  
Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM





--
Dr. André Matos
[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT through many databases

[Andre Matos]

Sounds interesting, but does the MERGER support complex SELECT  
statements and LEFT JOIN?


Andre


On 21-Nov-08, at 1:45 PM, Brent Baisley wrote:

On Fri, Nov 21, 2008 at 12:44 PM, Andre Matos [EMAIL PROTECTED] 
 wrote:

Hello,

Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They  
all have

the same structure but different data.

I would like perform this select

SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL

and collect the data from all 5 database. However, I would like to  
avoid

doing something like this:

SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL


Today I have 5, but tomorrow I can have 50 and I don't want to  
forget any

database.

Thanks for any help.

Andre


Create a MERGE table that is all those tables combined. Then you just
need to do 1 select as if it was one table. Just be sure to update the
MERGE table description when ever you add a table.

Brent Baisley


--
Dr. André Matos
[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]