how to improve mysql's query speed in table 'token' of keystone,when using in openstack with lots of VMs?

when i used mysql as the keystone's backend in openstack ,i found that the 'token' table saved 29 millions record (using myisam as engine,the size of token.MYD is 100G) and have 4 new token save per second. That result to the slow query of a token .since of inserting new token frequently,how cou

RE: Performance of delete using in

ginal Message- > From: Denis Jedig [mailto:d...@syneticon.net] > Sent: Wednesday, April 24, 2013 10:50 PM > To: mysql@lists.mysql.com > Subject: Re: Performance of delete using in > > Larry, > > Am 25.04.2013 02:19, schrieb Larry Martell: > > > delete

Re: Performance of delete using in

Larry, Am 25.04.2013 02:19, schrieb Larry Martell: delete from cdsem_event_message_idx where event_id in () The in clause has around 1,500 items in it. Consider creating a temporary table, filling it with your "IN" values and joining it to cdsem_event_message_idx ON event_id for deleti

Re: Performance of delete using in

I changed it to delete one row at a time and it's taking 3 minutes. On Wed, Apr 24, 2013 at 6:52 PM, Larry Martell wrote: > That is the entire sql statement - I didn't think I needed to list the > 1500 ints that are in the in clause. > > Also want to mention that I ran explain on it, and it is u

Re: Performance of delete using in

That is the entire sql statement - I didn't think I needed to list the 1500 ints that are in the in clause. Also want to mention that I ran explain on it, and it is using the index on event_id. On Wed, Apr 24, 2013 at 6:49 PM, Michael Dykman wrote: > You would have to show us the whole sql state

Re: Trouble with using "IN" for a sub-query statement

Chris wrote: Chris White wrote: On Monday 07 August 2006 12:13 pm, William DeMasi wrote: I meant to have it say : "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_act_id =24);" What does the output of (the subselect): select obj_

Re: Trouble with using "IN" for a sub-query statement

Chris White wrote: On Monday 07 August 2006 12:13 pm, William DeMasi wrote: I meant to have it say : "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_act_id =24);" What does the output of (the subselect): select obj_act_id from c2

Re: Trouble with using "IN" for a sub-query statement

On Monday 07 August 2006 12:13 pm, William DeMasi wrote: > I meant to have it say : > > "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from > > c2iedm_dev2.act_functl_assoc where subj_act_id =24);" What does the output of (the subselect): select obj_act_id from c2iedm_dev2.act_

Re: Trouble with using "IN" for a sub-query statement

On Monday 07 August 2006 12:02 pm, William DeMasi wrote: > The select statement I am trying to run is: > "select * from c2iedm_dev2.act where act_id =(select obj_act_id from > c2iedm_dev2.act_functl_assoc where subj_act_id =24);" Well the problem I'm seeing is that you

Trouble with using "IN" for a sub-query statement

server version for the right syntax to use near 'select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_" I have looked at the documentation for MySQL and this seems to be the correct syntax for using "IN". Could someone please let me know what is wrong? Thank you.

Re: Problem using IN statement MySQL 5

Paul Nowosielski <[EMAIL PROTECTED]> wrote on 01/11/2006 02:41:05 PM: > Hello, > > I'm trying to run q query with an IN statement in MySQL 5. Like so: > > SELECT * from encore enc, article art > WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) > > Its should return all the articles

Re: Problem using IN statement MySQL 5

Paul, SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) First, IN() accepts either a literal value list, or a subquery which returns a value list. Just passing it a table name won't work. Second, your join looks suspicious. Is this what you mean

Re: Problem using IN statement MySQL 5

Your select has two tables, but you don't join them. Can you provide the table info, and a subset of the data that should be pulled back by this query? David Paul Nowosielski wrote: Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, articl

Problem using IN statement MySQL 5

Hello, I'm trying to run q query with an IN statement in MySQL 5. Like so: SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) Its should return all the articles in the encore.articles column but instead only returns the first article. In encore,art

Re: Help with query using IN()

On 04/10/2005, Jasper Bryant-Greene wrote: > Kishore Jalleda wrote: > > Could you kindly advice if this query can be made to run faster > > SELECT title, template > > FROM template > > WHERE templateid IN > > (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,189 > > 0,1891,1902,1904

Re: Help with query using IN()

Kishore Jalleda wrote: Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106,2113,1982,1983

Help with query using IN()

Hi All, Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106,2113,1982,1983,1984,2074,623,21

re: using OR seems faster than using IN

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a query where I am fetching, in my test 68, rows. Using OR was faster by about 1.5 times, than the same query using IN. Should this be expected, using mysql 5.0.4, on Solaris 8, java 1.5. SELECT name, id FROM table WHERE idx=? OR idx=? OR

Re: Using <=> in WHERE vs HAVING clause

[EMAIL PROTECTED] wrote: > > > This is what I wound up going with: > > > > > > SELECT b.id, > > > if(a.a <=> b.a, NULL, b.a), > > > if(a.b <=> b.b, NULL, b.b), > > > if(a.c <=> b.c, NULL, b.c), > > > (NOT (a.a <=> b.a) AND > > > (a.b <=>

Re: Using <=> in WHERE vs HAVING clause

Rene Churchill <[EMAIL PROTECTED]> wrote on 02/22/2005 04:23:47 PM: > [EMAIL PROTECTED] wrote: > > Rene Churchill <[EMAIL PROTECTED]> wrote on 02/22/2005 03:39:05 PM: > > > Hi Shawn, > > > > > > This is what I wound up going with: > > > > > > SELECT b.id, > > > if(a.a <=> b.a, NULL,

Re: Using <=> in WHERE vs HAVING clause

Rene Churchill <[EMAIL PROTECTED]> wrote on 02/22/2005 09:21:29 AM: > Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21 > running on Mac OS X 10.3.7 > > I'm trying to compare two identical tables and find the rows > that are new/modified. I can't use a timestamp column because > t

Using <=> in WHERE vs HAVING clause

Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21 running on Mac OS X 10.3.7 I'm trying to compare two identical tables and find the rows that are new/modified. I can't use a timestamp column because the "new" table is constantly regenerated. So I'm using a large WHERE clause and t

Using <=> in WHERE vs HAVING clause

Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21 running on Mac OS X 10.3.7 I'm trying to compare two identical tables and find the rows that are new/modified. I can't use a timestamp column because the "new" table is constantly regenerated. So I'm using a large WHERE clause and t

Re: using IN()

> Put the 60K ints into a temporary table. Next join against this temporary > table. This join forces the optimizer to translate the range into an > eq_ref-one of the fastest type joins. He could also simply use a join to the ID table, provided that the 6 IDs are SELECTable in the current data

RE: using IN()

http://www.friendster.com > -Original Message- > From: Mitul Bhammar [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 17, 2004 9:22 PM > To: [EMAIL PROTECTED] > Subject: using IN() > > I've a bunch of IDs fetched(around 60,000) from a DB. > I'm using

Re: using IN() clause

On Wed, 17 Nov 2004 21:02:27 -0800 (PST), Mitul Bhammar <[EMAIL PROTECTED]> wrote: > The query is running fine for now. I wanted to know > how MySQL interprets and executes this query and can > it have problems in future?? Read about MySQL's query optimization here:

using IN() clause

I've a bunch of IDs fetched(around 60,000) from a DB. I'm using these IDs to fetch data from another DB having a related fields in its tables. I'm using IN clause for it. i.e. for e.g. SELECT * FROM site_users WHERE parentUserId IN (1,2,3,4) Again here parentUserId is Indexe

using IN()

I've a bunch of IDs fetched(around 60,000) from a DB. I'm using these IDs to fetch data from another DB having a related fields in its tables. I'm using IN clause for it. i.e. for e.g. SELECT * FROM site_users WHERE parentUserId IN (1,2,3,4) Again here parentUserId is Indexed

Re: how do i retrieve distinct rows using IN

On 15 Jan 2003, at 20:13, Ken Easson wrote: > SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN > ('item1', 'partA', 'partB', 'partB'); It's possible that the query you want is SELECT DISTINCT catNum, price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB'); or

Re: how do i retrieve distinct rows using IN

> >What is the problem, the business problem you are trying to solve? or what do >you need from your data? i think you are just querieng for the wrong >information .. but since i dont know what you are trying to do, i cant say >more right now. >Id need more information.. &

Re:how do i retrieve distinct rows using IN

If I understand you correctly you like to have the result for the specific table in your mail to look like this: 200 <-- this is the total price of item1 (1 * 200) 150 <-- this is the total prices of partAs (2 * 75) 300 <-- this is the total price of partBs (2 * 150) for this you are better o

Re: how do i retrieve distinct rows using IN

just querieng for the wrong information .. but since i dont know what you are trying to do, i cant say more right now. Id need more information.. Brian Lindner -- Original Message - Subject: how do i retrieve distinct rows using IN Date: Wed, 15 Jan 2003 15:12:37 -0400

Re: how do i retrieve distinct rows using IN

SELECT SUM( price ) from sys_bld_foo where catNum IN ('item1', 'partA', 'partB') group by catNum; Ken Easson wrote: hello, I have a list of things ('item1', 'partA', 'partB', 'partB') which relate to catNum items in a table sys_bld_foo. table sys_bld_foo: component: varchar 16 catNum: varchar

how do i retrieve distinct rows using IN

hello, I have a list of things ('item1', 'partA', 'partB', 'partB') which relate to catNum items in a table sys_bld_foo. table sys_bld_foo: component: varchar 16 catNum: varchar 16 price: decimal (10,2) i want to return 1 row for each item: when i use: SELECT DISTINCT price FROM sys_bld_foo WHE

RE: Internal Select statements using IN and NOT IN

Never mind, I figured it out by reading a previous question and I understand how to use Left join to do this. -Original Message- From: Alan Coleman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 5:22 AM To: [EMAIL PROTECTED] Subject: Internal Select statements using IN and

Internal Select statements using IN and 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 <> 'EXL') This function

mysqld dies on MATCH query using IN BOOLEAN MODE

Owen Borseth >Organization: SnapNames.com, Inc. >MySQL support: none >Synopsis: mysqld dies on MATCH query using IN BOOLEAN MODE >Severity: serious >Priority: high >Category: mysql >Class: bug >Release: mysql-4.0.1-alpha (Official MySQL

error using "IN BOOLEAN MODE"

Hello all, I searched the archives and found no similar problems, so here goes... Using an almost straight out of the manual example, I am unable to do a fulltext search using "IN BOOLEAN MODE" using the following SQL statement: SELECT * FROM parts WHERE MATCH (description) AGAINS

Re: Joining tables using "IN (...)" clause returns duplicate rows

On Monday 04 February 2002 01:12 pm, Greg Bailey wrote: > What is the real "production" version? ?If 4.0.2 can be called a > production version, I'd gladly use it on my web site; however, it > doesn't seem to indicate that on the MySQL home page. ?So if I find a > bug in 3.23.47 that was fixed a "

Re: Joining tables using "IN (...)" clause returns duplicate rows

I guess I'm a little confused about the MySQL versions. What is the real "production" version? If 4.0.2 can be called a production version, I'd gladly use it on my web site; however, it doesn't seem to indicate that on the MySQL home page. So if I find a bug in 3.23.47 that was fixed a "long ti

Problems using '*' in full text searches

Hi All... Here is my table... mysql> select * from SearchTest; +-++ | UID | SomeText | +-++ | 1 | Hello this is a test field | | 2 | And this is another field | | 3 | And because we are t

Re: Problem in using "in" keyword

only tell you that it works with that engine. It won't tell you anything about possible differences between the Linux and Windows versions of MySQL. Probably you're using IN in query that uses a sub-select. If that's true, the answer to your question can be found in the MySQL manua

RE: Problem in using "in" keyword

Hi, It would help if you can post the statement and the error message that you get. Regards Quentin -Original Message- From: TH Leung [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 12 December 2001 4:26 p.m. To: '[EMAIL PROTECTED]' Subject: Problem in using "in" keyw

Problem in using "in" keyword

Hi, I use the MySQLManager and enter a SQL statement with a "in" keyword, but an error message of syntax error occurs, I verify the SQL by plugging it to MS SQL Server 2000, it works without error, so I would like to know if the "in" keyword only functions on the Linux platform but not on the Win

Bad use of index when using IN

>Description: Requests are much longer when using IN instead of =, even if the list specified after IN has only one element. >How-To-Repeat: create table tmp (a integer, b integer, c integer); create index tmp_id on tmp (a,b,c); insert into tmp

RE: SELECT using IN on SET data

On 10-Aug-2001 [EMAIL PROTECTED] wrote: > Hi, > > Whats the most efficient way of doing a SELECT ... IN on a field of > datatype 'SET' ? > > Say you have four types of 'permissions': >- superuser >- poweruser >- normal >- guest > > Each user has a field, permis

SELECT using IN on SET data

Hi, Whats the most efficient way of doing a SELECT ... IN on a field of datatype 'SET' ? Say you have four types of 'permissions': - superuser - poweruser - normal - guest Each user has a field, permissions, of type SET ("superuser","poweruser","normal","gue

Re: I need help constructing a search thingy using % & _ in LIKE

yes, but then you wouldn't learn. if you don't want to buy Dubois' book, try appendix J of the mySql reference manual, available for free from the mysql site. At 06:06 PM 7/26/01 +0100, you wrote: >In message <[EMAIL PROTECTED]>, tj marlin ><[EMAIL PROTECTED]> writes >>I think it is time for

Re: I need help constructing a search thingy using % & _ in LIKE

In message <[EMAIL PROTECTED]>, tj marlin <[EMAIL PROTECTED]> writes >I think it is time for you to learn about regular expressions; page 520 >ff of Dubois. >(sql, mysql, database - to get past the filter) > any chance of an example or full syntax? M. -- Work:- postmasterAThinwick.demon.co

I need help constructing a search thingy using % & _ in LIKE

I think it is time for you to learn about regular expressions; page 520 ff of Dubois. (sql, mysql, database - to get past the filter) At 01:14 AM 7/26/01 +0100, you wrote: >Hi all, > >I have a problem, I have some VARCHAR data in a column called Sequence >looking like this:- > >5;6 >12;6

Re: I need help constructing a search thingy using % & _ in LIKE

I think it is time for you to learn about regular expressions; page 520 ff of Dubois. (sql, mysql, database - to get past the filter) At 01:14 AM 7/26/01 +0100, you wrote: >Hi all, > >I have a problem, I have some VARCHAR data in a column called Sequence >looking like this:- > >5;6 >12;6 >120;

I need help constructing a search thingy using % & _ in LIKE

Hi all, I have a problem, I have some VARCHAR data in a column called Sequence looking like this:- 5;6 12;6 120;6 5;6;1 12;6;12 120;6;105 So there could be anything from 2 to n numbers seperated by ; Each number could be 1 to 3 digits long (eg 5 12 or 121). I have tried various searches like

Re: Help with using IN Clause, does mysql support it???

At 7:04 PM -0400 7/13/01, <[EMAIL PROTECTED]> wrote: >Ok, I've been writing SQL for a few years now, but only recenlty started >using Mysql. I have a website hosted at a third party that uses mysql >3.23.36 > >I keep getting errors when I try to run fairly straight forward queries with >the IN cl

Help with using IN Clause, does mysql support it???

Ok, I've been writing SQL for a few years now, but only recenlty started using Mysql. I have a website hosted at a third party that uses mysql 3.23.36 I keep getting errors when I try to run fairly straight forward queries with the IN clause like: select var1 from table1 where var1 not in (sele