>>>> 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/tra
2013/06/11 12:59 -0700, Daevid Vincent
Also, just for S&G this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while "clever" is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare s
2013/06/11 12:59 -0700, Daevid Vincent
Also, just for S&G this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while "clever" is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare s
some rows in
another table (AND, not OR)
"I'm the ORIGINAL Rick James, B" (And, I'm still alive.) LOL
If you are using PHP, you might want to stop at 31 bits per INT/SET. PHP seems
not to yet be in the 64-bit world.
> -Original Message-
> From:
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
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 actually have 65 genres currently (with more to come
> I'm sure) LOL *FML*. I'm almos
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
ocedure to build the IN() lists within the first and second commands.
The value in the 'HAVING' clause in the last command (unless you use
the other option of reviewing the list of 'closest' matches) can be set
to the number of items in the "list of things to find" para
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,
&
AND sg.`genre_id` IN (
SELECT `genre_id` FROM `scenes_genres`
WHERE `genre_id` = 10
AND `genre_id` = 38
AND `genre_id` <> 22
AND `genre_id` <> 61
)
And straight up like this failure too...
JOIN `scenes_gen
WHERE `genre_id` = 10
AND `genre_id` = 38
AND `genre_id` <> 22
AND `genre_id` <> 61
)
And straight up like this failure too...
JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
AND (sg.`genre_id
That's exactly what I thought when reading Michael's email, but tried
anyways, thanks for clarification :)
2012/10/16
> 2012/10/16 12:57 -0400, Michael Dykman
> your now() statement is getting executed for every row on the select. try
> ptting the phrase up front
> as in:
> set @ut= u
2012/10/16 12:57 -0400, Michael Dykman
your now() statement is getting executed for every row on the select. try
ptting the phrase up front
as in:
set @ut= unix_timestamp(now())
and then use that in your statement.
Quote:
Functions that return the current date or time each are
Interesting thought, but I get the same result.
# Query_time: 0.001769 Lock_time: 0.001236 Rows_sent: 0 Rows_examined: 0
use kannel;
SET timestamp=1350413592;
select * from send_sms FORCE INDEX (priority_time) where time<=@ut order by
priority limit 0,11;
the MySQL i'm using is 5.5.28 from dotd
your now() statement is getting executed for every row on the select. try
ptting the phrase up front
as in:
set @ut= unix_timestamp(now())
and then use that in your statement.
On 2012-10-16 8:42 AM, "spameden" wrote:
Will do.
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
+---
Will do.
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
+-+-+
| Variable_name | Value
|
+-+-+
| back_log
On 10/15/2012 7:15 PM, spameden wrote:
Thanks a lot for all your comments!
I did disable Query cache before testing with
set query_cache_type=OFF
for the current session.
I will report this to the MySQL bugs site later.
First. What are all of your logging settings?
SHOW GLOBAL VARIAB
ptimizer's choice.
>
> ** **
>
> *From:* spameden [mailto:spame...@gmail.com]
> *Sent:* Monday, October 15, 2012 3:29 PM
>
> *To:* Rick James
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: mysql logs query with indexes used to the slow-log and not
> logging if
From: spameden [mailto:spame...@gmail.com]
Sent: Monday, October 15, 2012 3:29 PM
To: Rick James
Cc: mysql@lists.mysql.com
Subject: Re: mysql logs query with indexes used to the slow-log and not logging
if there is index in reverse order
Sorry, forgot to say:
mysql> show variables lik
ber 15, 2012 3:23 PM
To: Rick James
Cc: mysql@lists.mysql.com
Subject: Re: mysql logs query with indexes used to the slow-log and not logging
if there is index in reverse order
Sorry, my previous e-mail was a test on MySQL-5.5.28 on an empty table.
Here is the MySQL-5.1 Percona testing table:
mys
What percentage of time values meet the WHERE? This has a big impact
>> on the choice of explain plan and performance.
>>
>> * Set long_query_time = 0; to get it in the slowlog even if it is fast.
>> Then look at the various extra values (such as filesort, on disk, t
not a 3-digit integer, it is a full 32-bit integer (4 bytes).
> Perhaps you should have SMALLINT UNSIGNED (2 bytes).
>
> * BIGINT takes 8 bytes -- usually over-sized.
>
>
> > -Original Message-
> > From: spameden [mailto:spame...@gmail.com]
> > Sent: Monday, O
IGINT takes 8 bytes -- usually over-sized.
> -Original Message-
> From: spameden [mailto:spame...@gmail.com]
> Sent: Monday, October 15, 2012 1:42 PM
> To: mysql@lists.mysql.com
> Subject: mysql logs query with indexes used to the slow-log and not
> logging if there is
Hi, I've just checked on MySQL-5.5.28
it acts absolutely same.
I need to use (priority,time) KEY instead of (time, priority) because query
results in better performance.
With first key used there is no need to sort at all, whilst if using latter:
mysql> *desc select * from send_sms_test FORCE IN
Hi, list.
Sorry for the long subject, but I'm really interested in solving this and
need a help:
I've got a table:
mysql> show create table send_sms_test;
+---+
Hi,
My hosting company are only running MySQL version 5.0.77 and at this current
time are not planning on upgrading to at least my required version
of 5.1.43. My query in which I have some problems is shown below :
SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT
teams
In the last episode (Jun 27), mos said:
> I'm creating a MyISAM table using a Select statement as in:
>
> create table t2 select if(col1>col2,1.2,4.1) NewCol from t1
>
> (Ok, the computations are a lot more complicated than that).
>
> For some reason the column type
I'm creating a MyISAM table using a Select statement as in:
create table t2 select if(col1>col2,1.2,4.1) NewCol from t1
(Ok, the computations are a lot more complicated than that).
For some reason the column type of NewCol is Decimal and not Double. How do
I force the expression colu
I'm creating a table using a Select statement and several of the columns
are mathematical expressions. Unfortunately MySQL 5.0 creates them as
Decimal and I don't need that much precision. Is there a way to force MySQL
to create these columns as float?
Example:
create table x1 select 123.1*2.
Vincente Aggrippino <[EMAIL PROTECTED]> wrote on 01/12/2006 12:47:31
AM:
> On 1/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> >
> >
> > Vincente Aggrippino <[EMAIL PROTECTED]> wrote on 01/11/2006
11:33:38
> > PM:
> >
> > > I have three tables joined on key fields: delivery is joined
Hi,
2006/1/12, Vincente Aggrippino <[EMAIL PROTECTED]>:
> On 1/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> That fixed it... Thank you! But I don't understand how. Isn't my implicit
> inner join the same as the explicit one you used?
>
> I read Join Syntax in the ref. manual. Is it
On 1/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
>
> Vincente Aggrippino <[EMAIL PROTECTED]> wrote on 01/11/2006 11:33:38
> PM:
>
> > I have three tables joined on key fields: delivery is joined with
> > invoice_detail on delivery_id and with location on loc_id. I want to
> return
> >
Vincente Aggrippino <[EMAIL PROTECTED]> wrote on 01/11/2006 11:33:38
PM:
> I have three tables joined on key fields: delivery is joined with
> invoice_detail on delivery_id and with location on loc_id. I want to
return
> records of deliveries that have corresponding records in the location
tab
I have three tables joined on key fields: delivery is joined with
invoice_detail on delivery_id and with location on loc_id. I want to return
records of deliveries that have corresponding records in the location table,
but have no corresponding records in the invoice table.
Here's the query I'm a
Hello.
> Question 1: How do I check my syntax against the version and distribution
> that I am
> running?
In manual it is usually said which features are available as of which
version. Use the special form of comments to adopt your queries to
corresponding version. See:
http://dev.m
running: mysql Ver 12.22 Distrib 4.0.23, for Win95/Win98(i32) downloaded from
www.devside.net/web/servers/free/download.
When I run a script with the following commands I get sql syntax errors:
Create table 'test' ( 'helper' varchar(22) )
ENGINE=MYISAM DEFAULT CHARSET=latin1;
I get a sql error
Dear Paul,
Paul DuBois wrote:
At 19:52 +0300 2/9/05, George Chelidze wrote:
Hello,
I have the following problem: I have a table with a blob field and
turned on query logging. I'd like to log every query except inserts
into table with a blob field because my log files grow very fast and I
don't l
At 19:52 +0300 2/9/05, George Chelidze wrote:
Hello,
I have the following problem: I have a table with a blob field and
turned on query logging. I'd like to log every query except inserts
into table with a blob field because my log files grow very fast and
I don't like to see binary data in my l
Cron hack?
have a console running:
tail -f hostname.log | grep "tablename" > filter.log
-or-
tail -f hostname.log | grep -v "insert into blobtablename" > filter.log
> then have a cronjob every X minutes running: echo "" > hostname.log
To truncate the full querylog from getting too big?
No
Hello,
I have the following problem: I have a table with a blob field and
turned on query logging. I'd like to log every query except inserts into
table with a blob field because my log files grow very fast and I don't
like to see binary data in my log files, while other logs are very
informati
hello everybody,
We are working on mysql 4.1.8 version and windows XP platform.
As every one knew the default table type here is innodb.All innodb data files
and index files are stored in ibdata1 tablespace.
We started creating user defined tables called sam1 consisting of
IS there any way to get the 4.0.18/4.0 series to log client connects
(disconnects maybe too, but that's optional) connect errors (IE
password/auth failures) WITHOUT logging every query? Like every time a
client gets say
ERROR 1045: Access denied for user: [EMAIL PROTECTED]' (Using password: YE
Duncan Hill <[EMAIL PROTECTED]> wrote:
> Mysql version: 4.1.1
> Platform: Linux, pre-compiled RPMs from mysql.com
>
> My problem:
> Right now, I use a routine that selects the IDs that haven't been seen, and
> promptly does an insert into notifications_seen to flag that it has been
> seen. This
Duncan Hill <[EMAIL PROTECTED]> wrote:
> Mysql version: 4.1.1
> Platform: Linux, pre-compiled RPMs from mysql.com
>
[skip]
>
> My problem:
> Right now, I use a routine that selects the IDs that haven't been seen, and
> promptly does an insert into notifications_seen to flag that it has been
> s
Mysql version: 4.1.1
Platform: Linux, pre-compiled RPMs from mysql.com
Table 1:
CREATE TABLE `notifications` (
`recid` int(11) NOT NULL auto_increment,
`recdate` datetime NOT NULL default '-00-00 00:00:00',
`expiry` datetime default NULL,
`notify_title` varchar(150) default NULL,
`no
(8,2) | YES | | NULL| |
In your
mysql> desc log_book;
Cheers,
A
-Original Message-
From: landon kelsey [mailto:[EMAIL PROTECTED]
Sent: Sunday 21 December 2003 23:12
To: [EMAIL PROTECTED]
Subject: date and not null
why did this date work:
insert into log_book values ('
why did this date work:
insert into log_book values ('08-12-1973','C150','N5787G',1,1.8);
date looks good in table...has been rearranged to std format
and this didn't
insert into salesreps values (109,'Mary Jones',31,11,'Sales
Rep','10-12-1989',106,30.00,392725.00);
date is -00-00 in
Message-
> From: Doug Wolfgram [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 10, 2003 1:08 PM
> To: [EMAIL PROTECTED]
> Subject: AND NOT
>
>
> Is there any easy way to create this type of QUERY?
>
> I am trying to get a list of usernames from table A who do NOT
Is there any easy way to create this type of QUERY?
I am trying to get a list of usernames from table A who do NOT have a
linked record in Table B.
D
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
the EXISTS logic is ugly in as sql statement.
does that count? seems like ugly should count.
>
> hey, can i hijack my message back?...this thread is about the performance of
> subselects, not stored procedures. go write your own message :-)...
>
> sothe original question is if someone w
ot; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, November 12, 2002 12:02 AM
Subject: RE: RE: MySql 4.1 Sub Selects and not stored procedures
> I'm not sure where you're getting your information ("Typically, db vendors
> recommend you use an exists clause,
In the last episode (Nov 11), Greg Matthews said:
>
> hey, can i hijack my message back?...this thread is about the performance of
> subselects, not stored procedures. go write your own message :-)...
>
> sothe original question is if someone would be nice enough to answer..
>
> > Do any
0:0:02
Arthur
-Original Message-
From: Greg Matthews [mailto:greg55@;ozemail.com.au]
Sent: Monday, November 11, 2002 7:38 AM
To: [EMAIL PROTECTED]
Subject: Re: RE: MySql 4.1 Sub Selects and not stored procedures
hey, can i hijack my message back?...this thread is about the perform
hey, can i hijack my message back?...this thread is about the performance of
subselects, not stored procedures. go write your own message :-)...
sothe original question is if someone would be nice enough to answer..
> Do any MySql coders writing subselects in 4.1 know whether EXISTS will
Fabiana,
Monday, October 07, 2002, 1:51:06 AM, you wrote:
FMPdA> I'm trying to insert some data into a MySQL table and I'm just not getting
FMPdA> it to work. It's not a problem with the connection with the database because
FMPdA> I get to SELECT and show the data, but when I try to INSERT or UPD
Hello everybody,
I have a next problem to get a query working.
I have some tables. A User has Phone numbers and Calls associated with
each phone.
# Example: All the calls of the user number 1
SELECT
Call.*
FROM
User, Phone, Call
WHERE
Phone.User=1 AND
Call.Phone=P
Hi,
I'm trying to insert some data into a MySQL table and I'm just not getting
it to work. It's not a problem with the connection with the database because
I get to SELECT and show the data, but when I try to INSERT or UPDATE any
record, nothing happens...The weird thing is that it doesn't appear
> Hi,
>
> select table1.col_name FROM table1 left join table2
> on table1.col_name = table2.colnmae where table2.clnmae is null
>
> Rich
> Inbal Ovadia wrote:
>
>> Hi
>> i want to do in mysql query like that:
>>
>> SELECT col_name FROM table1 NOT IN (select col_nmae FROM tablse2)
>>
>> how can i
PROTECTED]
Subject: in and not in
Hi
i want to do in mysql query like that:
SELECT col_name FROM table1 NOT IN (select col_nmae FROM tablse2)
how can i do that?
thanks
-
Before posting, please check:
http://www.mysql.com
May 22, 2002 10:59 AM
Subject: in and not in
> Hi
> i want to do in mysql query like that:
>
> SELECT col_name FROM table1 NOT IN (select col_nmae FROM tablse2)
>
> how can i do that?
> thanks
>
>
>
>
Hi
i want to do in mysql query like that:
SELECT col_name FROM table1 NOT IN (select col_nmae FROM tablse2)
how can i do that?
thanks
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http:/
NOT IN
I've noticed that MySQL doesn't support the use of internal selects with the
in and not in methods such as in this example from a where statement.
and shift_id not in
(select shift_assoc from facdba.staff_schedule_temp
where status <&
I've noticed that MySQL doesn't support the use of internal selects with the
in and not in methods such as in this example from a where statement.
and shift_id not in
(select shift_assoc from facdba.staff_schedule_temp
where status <&
Hello!
I've been using MySQL version 3.22.32 and query with AND NOT phrase worked
fine. Now I have MySQL version 3.23.36 and the same query is losing records.
(the same data).
The query is quite complicated:
SELECT * FROM docs LEFT OUTER JOIN typy ON docs.nr = typy.nr LEFT
OUTER
Okay, password was not recognized because I had the tunnel working with the
f.q. server name rather than localhost (whereas the mysql client on the
slave will _only_ work with the tunnel if it is by the fq server name, since
localhost just goes to the local machine regardless of socket).
But reve
Sorry,
MySQL does not yet support sub-selects.
Cal
http://www.calevans.com
-Original Message-
From: Budiono Tjiayono [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 20, 2001 6:17 AM
To: [EMAIL PROTECTED]
Subject: Exists and Not exists
Hi there,
I am a mysql newbie. Is there anybody
Hi there,
I am a mysql newbie. Is there anybody that can help me solve this code, so that it can
run on mysql
$sql="CREATE TEMPORARY TABLE tempingr SELECT resepid FROM resep r WHERE NOT EXISTS
(SELECT * FROM what_ingr w WHERE w.resepid=r.resepid and NOT E
68 matches
Mail list logo
