y for num_of_day); expecting the
output: certain_day, pigId;from certain_day, pigId'weight increasing
continuously for num_of_day. How to select the records in one sql
statement?
thanks
I've thought about this a bit (since your question appeared on the list)
and I break dow
from certain_day, pigId'weight increasing
continuously for num_of_day. How to select the records in one sql
statement?
thanks
Dear friends,
1. https://bugs.mysql.com/bug.php?id=78934
2.
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id
The document (2) says that LAST_INSERT_ID() will not be changed if no rows
are inserted successfully. But (1) says that it's undefined when no r
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.it
s 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 dis
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 firs
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 st
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
On 2015-04-29 12:20 AM, 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"
Assuming a tab
Axel,
> Simply translated:
>
> select * from table t1
> where t1.data_value=1
> AND not exists(select * from table t2
>where t2.data_value=2
>and t2.item_number = t1.item_number)
Yes, but with t1 and t2 the same table.
best regards,
nt 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 rec
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:
>>> SELE
q 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
Simply translated:
select * from table t1
where t1.data_value=1
AND not exists(select * from table t2
where t2.data_value=2
and t2.item_number = t1.item_number)
Axel Diehl
__
GIP Exyr GmbH
Hechtsheimer Str. 35-37 | 55131 Mainz
Tel: +49 (0
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
> 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
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
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
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
On Feb 17, 2014, at 12:36 PM, Roy Lyseng wrote:
> Try... `ip` IN (SELECT ip FROM temp_ip)
Wow! Only 1 second to return the results now!! That's 15x faster!!!
> Each subquery needs to be a complete SELECT query.
That's good to know. I figured that since temp
Hi Jennifer,
On 2/17/2014 3:11 PM, Jennifer wrote:
CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
GROUP BY `ip`
HA
Hi Jennifer,
great that it worked.
Try replacing the line
`ip` IN (temp_ip)
with
`ip` IN (SELECT ip FROM temp_ip)
Each subquery needs to be a complete SELECT query.
Thanks,
Roy
On 17.02.14 21:11, Jennifer wrote:
On Feb 17, 2014, at 10:17 AM, Roy Lyseng wrote:
please try filtering
However, I did try to see if I could do it, but there's an error
somewhere in the SQL. What am I doing wrong?
CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
A
Hi Jennifer,
please try filtering with a subquery that locates ip addresses with more than 1
attempt:
SELECT ip, page, url, time_stamp
FROM ip_adresses
WHERE AND
ip IN (SELECT ip
FROM ip_addresses
WHERE
GROUP BY ip
HAVING COUNT
On Feb 12, 2014, at 6:30 PM, Larry Martell wrote:
> Try adding a having clause, e.g.:
>
> SELECT `ip`,`page`,`url`,`time_stamp`
> FROM `ip_addresses`
> WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
> CURDATE() - INTERVAL 1 SECOND)
> AND TRIM(`referr
nly want to report on multiple hits
> from the same IP address - not just a single hit by someone.
>
> How can I add a condition to only show hits by someone who's hit the
> site 2 or more times with the same IP? I tried GROUP BY but that didn't
> return all the hit
ingle hit by someone.
How can I add a condition to only show hits by someone who's hit the
site 2 or more times with the same IP? I tried GROUP BY but that didn't return
all the hits - one one per IP.
SELECT `ip`,`page`,`url`,`time_stamp`
FROM `ip_addresses`
WHERE (`time_st
2013/06/13 23:08 +, Rick James
FIND_IN_SET might work the cleanest...
WHERE FIND_IN_SET('action', genres) OR/AND [NOT] ...
And have genres look like 'action,drama,foobar', that is comma-separators, and
no need for leading/trailing comma.
That would also work for genres = '1,3,10,19,
ires a
file_sort table scan to compare substrings basically...
SELECT * FROM scene_all_genres WHERE scene_id = 17;
scene_id genres
----
17 1|3|10|19|38|53|58|59|
SELECT * FROM scene_all_genres
ires a
file_sort table scan to compare substrings basically...
SELECT * FROM scene_all_genres WHERE scene_id = 17;
scene_id genres
----
17 1|3|10|19|38|53|58|59|
SELECT * FROM scene_all_genres
I am so, so glad that someone finally said what I think each time I see a
message from you Mr. James.
Original message
From: Rick James
Date: 06-12-2013 8:45 PM (GMT-04:00)
To: Daevid Vincent ,mysql@lists.mysql.com
Subject: RE: How do I select all rows of table that have
ent: Wednesday, June 12, 2013 11:26 AM
> To: mysql@lists.mysql.com
> Subject: RE: How do I select all rows of table that have some rows in
> another table (AND, not OR)
>
> This idea is so fancy pants and clever I *wish* it could have worked for
> me.
> I checked and we actual
Oh! I must have misread. I didn't see how you had a solution for > 64 bits.
I may have to experiment with that!
> -Original Message-
> From: Daevid Vincent [mailto:dae...@daevid.com]
> Sent: Wednesday, June 12, 2013 11:26 AM
> To: mysql@lists.mysql.com
> Subject:
ne 12, 2013 9:39 AM
> To: Daevid Vincent; mysql@lists.mysql.com
> Cc: 'shawn green'
> Subject: RE: How do I select all rows of table that have some rows in
> another table (AND, not OR)
>
> Thinking out of the box... (And posting my reply at the 'wrong' end o
Hello Daevid,
On 6/11/2013 7:17 PM, Daevid Vincent wrote:
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows in
another table (AND
re# field. I would use 32 and INT UNSIGNED.)
> -Original Message-
> From: Daevid Vincent [mailto:dae...@daevid.com]
> Sent: Tuesday, June 11, 2013 4:17 PM
> To: mysql@lists.mysql.com
> Cc: 'shawn green'
> Subject: RE: How do I select all rows of table that have some ro
> -Original Message-
> From: shawn green [mailto:shawn.l.gr...@oracle.com]
> Sent: Tuesday, June 11, 2013 2:16 PM
> To: mysql@lists.mysql.com
> Subject: Re: How do I select all rows of table that have some rows in
> another table (AND, not OR)
>
> Hello Daevid,
&
Hello Daevid,
On 6/11/2013 3:59 PM, Daevid Vincent wrote:
I am trying to implement a filter so that a user could select various genres
they want "in" or "out". Perhaps they like 'action' and 'car chases' but
don't like 'foreign' and '
I am trying to implement a filter so that a user could select various genres
they want "in" or "out". Perhaps they like 'action' and 'car chases' but
don't like 'foreign' and 'drifting' (or whatever...)
So I want something sort of l
> 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 wor
enstag, 05. Februar 2013 um 15:49 Uhr
Von: cl
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,
Try using a CASE construct in the select. Should work for this.
A
On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn 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
&
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" OR
>>>> 2013/02/02 12:58 -0600, Peter Brawley >>>>
On 2013-02-01 10:18 PM, h...@tbbs.net wrote:
>>>>>2013/01/31 22:24 -0600, Peter Brawley >>>>
>Is this what you mean?
>
>Select,
>pricelist
>If( !IsNull(specialprice) And specialpri
On 2013-02-01 10:18 PM, h...@tbbs.net wrote:
2013/01/31 22:24 -0600, Peter Brawley >>>>
Is this what you mean?
Select,
pricelist
If( !IsNull(specialprice) And specialprice < unitprice And CurDate() Between
startingDate And endingDate,
specialprice,
unitprice
) as used_pric
>>>> 2013/01/31 22:24 -0600, Peter Brawley >>>>
Is this what you mean?
Select,
pricelist
If( !IsNull(specialprice) And specialprice < unitprice And CurDate() Between
startingDate And endingDate,
specialprice,
unitprice
) as used_price
>From catalog
Where itemid=&#x
On 2013-01-31 8:13 PM, Angela Barone wrote:
Hello,
I'm trying to write a select query that grabs two prices from my db and
displays them on a web page. I want it to grab the `listprice`, and either the
`unitprice` or the `specialprice` depending on the following criteria:
i
Hello,
I'm trying to write a select query that grabs two prices from my db and
displays them on a web page. I want it to grab the `listprice`, and either the
`unitprice` or the `specialprice` depending on the following criteria:
if the `specialprice` is not empty,
AND it's
Hi Neil,
On 11/22/2012 7:14 PM, h...@tbbs.net wrote:
2012/11/22 14:30 +, Neil Tompkins >>>>
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
From this I what to
TABLE `test`.`atest` (
> `id` int(10) unsigned NOT NULL,
> `type` int(10) unsigned NOT NULL,
> PRIMARY KEY (`id`) USING BTREE
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
> insert into atest(id,type)
> values(1000,5)
> ,(1001,5)
> ,(1002,2)
> ,(1001,2)
> ,(1003,2)
&
Claudio
This is the solution i decided to go for as provided in a previous response.
Thanks
Neil
On 23 Nov 2012, at 00:41, Claudio Nanni wrote:
> On 11/22/2012 04:10 PM, Ben Mildren wrote:
>> SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
> Ben you were almost there ;)
into atest(id,type)
values(1000,5)
,(1001,5)
,(1002,2)
,(1001,2)
,(1003,2)
,(1005,2)
,(1006,1);
SELECT DISTINCT id
FROM atest
WHERE `type` = 2 OR `type` = 5
GROUP BY id
HAVING count(DISTINCT `type`) = 2;
On Thu, November 22, 2012 22:16, Michael Dykman wrote:
> Mogens,
>
> Platform cou
On 11/22/2012 04:10 PM, Ben Mildren wrote:
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
Ben you were almost there ;)
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)=
The only bad is the hardcoded parameter in the HAVING, may be it might be
improved
>>>> 2012/11/22 14:30 +, Neil Tompkins >>>>
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
>From this I what to get a distinct list of id whe
>>>> 2012/11/22 14:30 +, Neil Tompkins >>>>
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
>From this I what to get a distinct list of id whe
u are attending.
>
> All necessary information to provide a sufficient help.
>
>>
>> On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
>> wrote:
>>
>>> SELECT DISTINCT id FROM table WHERE type IN ('2','5')
>>>
>>> should wor
e attending.
All necessary information to provide a sufficient help.
>
> On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski
> wrote:
>
>> SELECT DISTINCT id FROM table WHERE type IN ('2','5')
>>
>> should work
>>
>>
>> On 22 November 20
Hmmm.
OR, IN and HAVING pops up.
On Thu, November 22, 2012 15:30, Neil Tompkins wrote:
> Hi,
>
> I'm struggling with what I think is a basic select but can't think how to
> do it : My data is
>
> id,type
>
> 1000,5
> 1001,5
> 1002,2
> 1001,2
> 10
On Thu, Nov 22, 2012 at 11:58 AM, Neil Tompkins
wrote:
>
> By unique you mean that no id and type would be duplicated like
>
> 1,1
> 1,1
>
> Yes it isn't possible for duplicate id and type in more than 1 row
Yes, that's exactly what I meant.
- mdyk...@gmail.com
May the Source be with you.
--
Doing a EXPLAIN on the SELECT statement it is using "Using where; Using
temporary; Using filesort" with 14000 rows of data. How best to improve
this; when I already have indexed on id and type
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman wrote:
> Assuming that (id,type) is
Ignore that it does work fine. Sorry
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman wrote:
> Assuming that (id,type) is unique in the source data, that is a pretty
> elegant method:
>
> > select id from
> > (select distinct id, count(*)
> > from my_table
> > wh
When trying this query I get
FUNCTION id does not exist
On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman wrote:
> select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
>
gant method:
>
> > select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
> >
>
> --
> - michael dykman
> - mdyk...@gmail.com
>
> May the Source be with you.
>
Assuming that (id,type) is unique in the source data, that is a pretty
elegant method:
> select id from
> (select distinct id, count(*)
> from my_table
> where type in (2,5)
> group by id
> having count(*) = 2)a;
>
--
- michael dykman
- mdyk...@gmail.com
May t
Having watched responses go back and forth, I'll throw my cave-man approach
into the mix.
select id from
(select distinct id, count(*)
from my_table
where type in (2,5)
group by id
having count(*) = 2)a;
And addressing one of your concerns about more than two variables...in this
exampl
*HAVING typelist = 'x,y,z';
On 22 November 2012 15:25, Ben Mildren wrote:
> Ah read it quickly and misread your requirement. Joins are likely FTW
> here. The alternative would be to do something like this, but I'd opt
> for the joins if you have a reasonably sized
indexed, yes?)
As you no doubt have noticed, the problem with these solutions:
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
is that they returns ids that have ANY of those values which is not
what you are asking for,
If your dataset is HUGE, there might be a performance problem
Ah read it quickly and misread your requirement. Joins are likely FTW
here. The alternative would be to do something like this, but I'd opt
for the joins if you have a reasonably sized data set:
SELECT id, GROUP_CONCAT(type ORDER BY type) AS typelist FROM mytable
WHERE id IN(x,y,z) GROUP
Do you know if I had multiple joins there would be a performance issue ?
On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman wrote:
> Keep joining I think. In the absence of intersect (which incurs the cost
> of a query per type anyhow ), this join pattern is the only option I can
> think of.
>
> O
SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
On 22 November 2012 15:01, Neil Tompkins wrote:
> Michael,
>
> Thanks this kind of works if I'm checking two types. But what about if I
> have 5 types ?
>
> On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman wrote:
&
Keep joining I think. In the absence of intersect (which incurs the cost of
a query per type anyhow ), this join pattern is the only option I can think
of.
On 2012-11-22 10:01 AM, "Neil Tompkins"
wrote:
Michael,
Thanks this kind of works if I'm checking two types. But what about if I
have 5 ty
as opposed to type=5 OR type=2;
>
> in some dialect of SQL (not mysql) you can do this:
> select distinct id from 'table' where type=5
> intersect
> select distinct id from 'table' where type=2
>
>
> As INTERSECT is not avilable under mysql, we will h
How about if I have the following
SELECT DISTINCT id
FROM my_table
WHERE (type = 3 OR type = 28 OR type = 1)
In this instance, for the id 280149 it only has types 3 and 28 but *not *1.
But using the OR statement returns id 280149
On Thu, Nov 22, 2012 at 2:53 PM, Benaya Paul wrote:
>
U can remove the type field it will work
On Nov 22, 2012 8:21 PM, "Neil Tompkins"
wrote:
> Basically I only what to return the IDs that have both types.
>
>
> On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski >wrote:
>
> > SELECT DISTINCT id FROM table WHERE typ
response did not go to the list..
I assume that you mean the id must be associated with both type=5 AND
type=2 as opposed to type=5 OR type=2;
in some dialect of SQL (not mysql) you can do this:
select distinct id from 'table' where type=5
intersect
select distinct id from 'tabl
Basically I only what to return the IDs that have both types.
On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski wrote:
> SELECT DISTINCT id FROM table WHERE type IN ('2','5')
>
> should work
>
>
> On 22 November 2012 14:30, Neil Tompkins wrote:
>
>>
Hi Neil
Would something like this work.
SELECT DISTINCT id,type from your_table WHERE type=2 OR type=5;
Mike
- Original Message -
From: "Neil Tompkins"
To: "[MySQL]"
Sent: Thursday, November 22, 2012 9:30 AM
Subject: Basic SELECT help
Hi,
I'm struggli
SELECT DISTINCT id FROM table WHERE type IN ('2','5')
should work
On 22 November 2012 14:30, Neil Tompkins wrote:
> Hi,
>
> I'm struggling with what I think is a basic select but can't think how to
> do it : My data is
>
> id,type
>
&g
Hi,
I'm struggling with what I think is a basic select but can't think how to
do it : My data is
id,type
1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1
>From this I what to get a distinct list of id where the type equals 2 and 5
Any ideas ?
Neil
Even if you could block them, they would be easy to get around:
SELECT * FROM tbl WHERE 1;
If you have long running queries, you should investigate the reasons (other
than lack of WHERE).
* MyISAM locks the table for any writes. This prevents a SELECT from starting
or a select can prevent
age-
> From: Martin Gainty [mailto:mgai...@hotmail.com]
> Sent: 24 September 2012 04:58
> To: fuller.art...@gmail.com; pownall...@gmail.com
> Cc: mysql@lists.mysql.com
> Subject: RE: How to block SELECT * FROM table; but not SELECT * FROMT
> table WHERE...;
>
>
> Possi
Possibly run your constructed query thru a regex expression e.g.
String mydata = "SELECT * from table WHERE a Date: Sun, 23 Sep 2012 18:38:58 -0400
> Subject: Re: How to block SELECT * FROM table; but not SELECT * FROMT table
> WHERE...;
> From: fuller.art...@gmail.com
> To: pow
Tim,
I think you misunderstood the question. Daniel wants to block Select
queries that ask for all rwows, and permit only queries that ask for some
rows, as restricted by the Where clause.
Unfortunately, I don't think that can be done. But I'm not certain of that;
there might be a tric
since 10 years
* mainmenu: select id,title from table order by sortfield;
* and YES in the backend the is no single reson to reduce the result
* and NO using a key would not make anything faster
so why would there be a WHERE make any sense and why does
someone like to break the application just for
select * from table where column=value means it will return only rows that
match. as long as you have proper indexing there should not be any issues.
On Sun, Sep 23, 2012 at 1:23 PM, Luis Daniel Lucio Quiroz <
luis.daniel.lu...@gmail.com> wrote:
> Helo,
>
> Just wondering if
Am 23.09.2012 20:23, schrieb Luis Daniel Lucio Quiroz:
> Just wondering if is possible to block SELECT queries that doesnt have
> a WHERE statement within.
no and the idea is broken by design
what is wrong with a "select * from table" with small
tbales having only a handful
Helo,
Just wondering if is possible to block SELECT queries that doesnt have
a WHERE statement within.
LD
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
_ID | Duration | Query
> > |
> >
> +--++------+
> > |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
> > cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
>
column used in the order by caluse, should be the first column in the
select statement to make the index work
On Wed, Jul 11, 2012 at 3:16 PM, Reindl Harald wrote:
>
>
> Am 11.07.2012 11:43, schrieb Ewen Fortune:
> > Hi,
> >
> > On Wed, Jul 11, 2012 at 10:31 AM, Reind
-+
> | Query_ID | Duration | Query
> |
> +--++------+
> |1 | 0.32261700 | SELECT SQL_NO_CACHE * FROM
> cms1_quickbar_groups force key (qbq_key) ORDER BY qg_sort ASC |
> |2 | 0.245
ofiles;
+--++--+
| Query_ID | Duration | Query
|
+--++--+
|1 | 0.32261700 | SELECT SQL_NO_CACHE
the mysql query optimizer is somehow stupid
a simple query, order by with a indexed column and
you have to use where order_by_field>0 - why the
hell is mysqld not happy that a key is on the field
used in "order by"?
mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups ORDE
plain plan. Also you can
retrieve specific columns on which indexes are created to use the feature
of "Covering index".
On Wed, Jul 11, 2012 at 3:19 AM, Reindl Harald wrote:
> my reason for create a key on "qg_sort" was primary
> for this query - but why is here &#x
my reason for create a key on "qg_sort" was primary
for this query - but why is here 'filesort' used?
mysql> EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
++-+--+--+---+--+-+--+--
INTEGER);
Client 1:
SELECT * FROM foo WHERE i = 0;
SELECT * FROM foo WHERE i = 0;
Client 2:
UPDATE foo SET j = 1 WHERE i = 0;
Suppose the table starts out with a single tuple (0, 0). Now, if client 1
and client 2 are running at the same time, wrapping client 1's select
statements with a BEGIN/C
Hello
COMMIT statements may or may not force the database to call fflush() to flush
your double-write to disk. This may or may not affect your performance,
depending on your scale, traffic, and how much you're trying to squeeze your
hardware. If you're working on the borderline like I am, benc
I vote 1) yes 2) no
It could be result of the app developer's convenience to just wrap anything
they submit to the database in a transaction. Selects are not transaction but
autocommit/commit do no harm. That might be the thinking.
On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:
> We
We have an application with blocks of code that begin with setting autocommit
off, and end with a commit. The code in between does only selects, no updating.
1) Am I correct in thinking that the autocommit and commit statements
don't really accomplish anything useful?
2) If the autoc
2012/03/01 19:56 -0800, Don Wieland
I do not get the same results. Am I missing something? Hopefully
something simple ;-)
O, you are. You do not want GROUP_CONCAT in the subquery. It gives you the
comma-separated string whereto you referred, which, as far as the IN goes, is
o
how a result
if one or more of the payees are different than the invoice's client_id.
So now with the mySQL queries that are working:
First of all, the client_id of the invoice I am querying on is 251719.
query 1 = select group_concat(payment_id) from tl_trans_pmt_items
where inv_id
Thanks, it seems to be working now. I just discovered "WITH ROLLUP". It made me
very
happy on this project...
On 2/8/12 2:54 AM, Arthur Fuller wrote:
I'm not sure your method isn't working, but try changing changing the "to date" part to
"< '2012-02-08' " and see what you get.
HTH,
Arthur
1 - 100 of 4947 matches
Mail list logo
