RE: How to get previous and next result

[Christopher Knight]

next:
select cols from table where id > YOUROLDID and forums = CURRENT_FORUM order by id 
limit 1
previous:
select cols from table where id < YOUROLDID and forums = CURRENT_FORUM order by id 
desc limit 1

however, this may not be as fast or efficient enough
ou might want to create another id field that is a 'counter' for the messages in each 
forum.
so instead of keeping track of the overall message id, keep track of the forum and the 
message id for that forums
ie

UMID = unique message id
FMID = forum message id
fid = forum id

UMIDFMIDFID
1   1   a
2   2   a
3   3   a
4   1   b
5   1   c
6   2   b
7   4   a
8   3   b

If this doesnt make any sense... it did in my head and Im probably doing a bad job of 
explaining it.

Good luck


-Original Message-
From: Maria Garcia Suarez [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 09, 2003 10:53 AM
To: [EMAIL PROTECTED]
Subject: How to get previous and next result


Hi there!

I'm currently developing a web where we let users
create their own forums. All the messages (wherever
they are posted) are stored in the same mysql table.

When users read a certain message I would like to
display the previous and next message in that forum.

Since all the messages (of the different forums) are
stored in the very same table I don't find how to
guess what's the ID of the previous and next message.

Is there any way to find those IDs?

Thanks.

Kisses,
Maria

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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



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

RE: Huge Server configuration

[Christopher Knight]

BEGIN my.cnf
[mysqld]
port  = 3306
socket= /tmp/mysql.sock
basedir   = /usr/local/mysql

log   = /var/log/mysql/mysql.log
log-slow-queries  = /var/log/mysql/mysql-slow.log
log-err   = /var/log/mysql/mysql.err
log-bin   = /var/log/mysql/mysql-bin.log
log-long-format

skip-locking

##Change depending on situation
transaction-isolation = READ-COMMITTED

###Tweak Here##
set-variable  = sort_buffer=512M
set-variable  = record_buffer=512M
set-variable  = key_buffer=256M

set-variable  = log-warnings=1
set-variable  = long_query_time=30

###FOR CACHED QUERIES###
set-variable  = query_cache_size=128M

###TUNE BASED ON CONNECTIONS###
set-variable  = max_allowed_packet=32M
set-variable  = max_connections=50
set-variable  = thread_stack=64K
set-variable  = thread_cache=16
set-variable  = thread_concurrency=8


# InnoDB Config


###If you change these... make sure you have a backup !!! (before)
###These are just setup things... not really tweak
  #innodb_data_home_dir  = /usr/local/mysql/data/innodb
  #innodb_data_file_path = innodb1:500M:autoextend
  #innodb_log_group_home_dir   = /var/log/mysql/innodb
  #innodb_log_arch_dir   = /var/log/mysql/innodb/
  #set-variable  = innodb_log_files_in_group=3
  #set-variable  = innodb_log_file_size=10M
  #set-variable  = innodb_log_buffer_size=10M

set-variable  = innodb_lock_wait_timeout=10

###Look At These too!!
set-variable  = innodb_buffer_pool_size=512M
set-variable  = innodb_additional_mem_pool_size=128M
set-variable  = innodb_flush_log_at_trx_commit=0

END my.cnf

>what table types? Innodb.etc...
It is mainly innodb.

>about how many tables?
there are around 200 tables

>do you do alot of sorting?
Lots of sorting and fltering is done

>are the exact same queries repeated alot?
Not likely

>is the machine doing anything else or is mainly a DB server?
Nope. Just DB.

>can I borrow the machine for awhile?
Nope. It is our to be production box

>what version of mysql are you running?
4.0.14


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

RE: Huge Server configuration

[Christopher Knight]

what table types? Innodb.etc...
about how many tables?
do you do alot of sorting?
are the exact same queries repeated alot?
is the machine doing anything else or is mainly a DB server?
can I borrow the machine for awhile?
what version of mysql are you running?

chris


-Original Message-
From: Mysql List [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 11:38 AM
To: [EMAIL PROTECTED]
Subject: Huge Server configuration


Hello all,

I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM 
and 1TB harddisk space.
All the tables size are over 10GB and they have over 100mm records.

Could some one help me get an appropriate mysql configuration(my.conf) 
file for the machine.

I  understand ther are lots of factors depends on it to get a steady 
working configuration.
All I need is some model configuration. I think later on I can tune thar up.

Thanx in Advance
-Chandra




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



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

RE: What is a good benchmark?

[Christopher Knight]

>Is it me or do these dual athlons seem rather responsive!

Yay!! I win (so far... heh)!
I ran it on one of our servers (not idle... running apache w/ CGIs and db calls ~ 
140,000 scripts/day )
We are using as many tricks as we can.
0. mysql 4.0 binary from mysql.com
1. we set the nice to -15 in safe_mysqld (good trick.. be must be carefull not 
to over do it)
2. my.cnf
skip-locking
set-variable  = lower_case_table_names=1
set-variable  = sort_buffer=50M
set-variable  = record_buffer=2M
set-variable  = key_buffer=64M
set-variable  = query_cache_size=5M
set-variable  = thread_stack=2K
set-variable  = thread_cache=8
set-variable  = thread_concurrency=4
3. Kernel is custom compiled with minimal drivers. Only
what hardware is on the system is compiled. No modules.
4. I think the Athlons just preform better too. 

>I use mySQL 4.0.13 running on FreeBSD 4.8.
>Pentium 4 2.8Ghz
>2GB DDR400
>1 row in set (0.7 sec) | (0.68 sec) | (0.68 sec)
>Andrew.

> 1 row in set (0.54 sec)
> 2x Athlon2100
> 2GB Ram
> Linux 2.4.20
> Chris

> pIII 500
> 1 row in set (2.59 sec)
> Jake Johnson


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

RE: What is a good benchmark?

[Christopher Knight]

1 row in set (0.54 sec)

2x Athlon2100
2GB Ram
Linux 2.4.20

-Original Message-
From: Jake Johnson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 23, 2003 9:34 AM
To: Mysql
Subject: What is a good benchmark? 


I ran this benchmark on my pIII 500 and was wondering what everyone else
was getting?

mysql> SELECT BENCHMARK(100,ENCODE("hello","goodbye"));

+--+
| BENCHMARK(100,ENCODE("hello","goodbye")) |
+--+
|0 |
+--+
1 row in set (2.59 sec)


Regards,
Jake Johnson
[EMAIL PROTECTED]

__
Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on
Rims, Car Audio, and Performance Parts.


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



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

RE: grouping / sorting etc..

[Christopher Knight]

Thanks for everybody's help!!
This way worked for me.  Clever!

Chris

... and yes... even my example was messed up
:-P

-Original Message-
From: Rudy Metzger [mailto:[EMAIL PROTECTED]
Sent: Monday, July 14, 2003 4:15 AM
To: Christopher Knight; MySQL List
Subject: RE: grouping / sorting etc..


SELECT col1, IF(count(*)!=count(col2),NULL,'BAD')
  FROM table1
 GROUP BY col1;

This ONLY works EXACLTY for the case you submitted.

Please note that your example is wrong (I think). 3 should also return
NULL, shouldn't it?

/rudy



-Original Message-----
From: Christopher Knight [mailto:[EMAIL PROTECTED] 
Sent: vrijdag 11 juli 2003 17:32
To: MySQL List
Subject: grouping / sorting etc..

Im having troubles getting the results I want... can someone suggest
which way to go..
mysql 4.0

A

| 1 |  |
| 1 | BAD  |
| 2 |  |
| 3 | BAD  |
| 3 |  |
| 4 | BAD  |
| 5 |  |
| 5 | BAD  |
| 5 | BAD  |


what I want is 5 results.  If there is a NULL, then return the NULL,
else return the BAD

so basically return ...

| 1 |  |
| 2 |  |
| 3 | BAD  |
| 4 | BAD  |
| 5 |  |


Ive tried 

select * from table group by 1stcolumn = (unpredicatble results,
but returns 5 rows)
select * from talbe groub by 1stcolumn, 2ndcolumn = (returns 8
rows)

I could do the 2nd way then filter out w/ the code...
Is there a better way?
Thanks
Chris


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


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



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

grouping / sorting etc..

[Christopher Knight]

Im having troubles getting the results I want... can someone suggest which way to go..
mysql 4.0

A

| 1 |  |
| 1 | BAD  |
| 2 |  |
| 3 | BAD  |
| 3 |  |
| 4 | BAD  |
| 5 |  |
| 5 | BAD  |
| 5 | BAD  |


what I want is 5 results.  If there is a NULL, then return the NULL, else return the 
BAD

so basically return ...

| 1 |  |
| 2 |  |
| 3 | BAD  |
| 4 | BAD  |
| 5 |  |


Ive tried 

select * from table group by 1stcolumn = (unpredicatble results, but returns 5 
rows)
select * from talbe groub by 1stcolumn, 2ndcolumn = (returns 8 rows)

I could do the 2nd way then filter out w/ the code...
Is there a better way?
Thanks
Chris


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

RE: mysqld question

[Christopher Knight]

Not a silly question... we all had to start somewhere...
you can do it during the create table ( primary key )
http://www.mysql.com/doc/en/CREATE_TABLE.html

or after
http://www.mysql.com/doc/en/CREATE_INDEX.html

what indexes do...
  lets say you do ALOT of queries on column user_id in the table user...
 "select * from user where user_id = x" or you sort,join,match on user_id
alot.
Then indexes will use a little more memory and it will make those queries a
little (sometimes ALOT) faster.

However, if you are inserting new rows ALOT vs selects... then indexes might
actually slow you down a little because the database has to write to the
table and then write to the index on each insert...

So you dont want to add indexes on EVERY column... just the ones that you
select or sory on most of the time.

Good Luck

Also check this out for a little help
http://www.mysql.com/doc/en/Tips.html

-Original Message-
From: Henrik Leghissa [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 24, 2003 9:42 AM
To: Christopher Knight
Subject: RE: mysqld question


At 16:39 2003-06-24, Christopher Knight wrote:
>it really depends on
>  how big your database is
>  what else is running on the server (apache..)

Well, it has some free capacity, both cpu and memory-wise.


>  what version are you running

4.x


>  what is your mom's maiden name
>  if you look at the sun, how long to you blink
>
>:-)

haha


>  Id start w/ making sure you have indexes.  They help abunch.

Pardon my stupid question, but where do I set these? (and the syntax etc)


Thanks.



  / H



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

RE: mysqld question

[Christopher Knight]

it really depends on 
 how big your database is
 what else is running on the server (apache..)
 how many connections at once
 percentage of selects to updates/inserts
 do you have indexes
 what version are you running
 what is your mom's maiden name
 if you look at the sun, how long to you blink

:-)
 
 Id start w/ making sure you have indexes.  They help abunch.
 


-Original Message-
From: Henrik Leghissa [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 24, 2003 9:26 AM
To: [EMAIL PROTECTED]
Subject: Re: mysqld question


At 16:21 2003-06-24, gerald_clark wrote:
>Linux shows threads.
>BSD shows processes.
>
>The difference is in the way ps runs, not mysqld.

*doh* I guess the sun got to me, sorry mate! :-)

But do you have any good ways to get the database to run any faster? I am 
running it on a really beat up piece of junk (p200 with 98MB in ram, don't 
tell me to get new hardware - 'cause I will! hehe) and would like some 
hands on settings in the cnf-file from you if possible.



  / H 


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



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

RE: Textfile to a 2 column mysql database

[Christopher Knight]

OR, if you are good at vi,

you can insert a ' at the begining and end of every line (if you dont have
any 's in the file)
then put a , at the end of every odd line
then join every other line

the put a   "insert into blah (question, answer) values ( "   at the
begin of every line
and then a ");" at the end of every line

Then you hopefully have a file full of insert statements and you can just
feed it into a mysql client.
If I missed a step or added one by accident or even got one wrong, go ahead
and fix it and pretend
I told you correctly. ;-)

The problem with my solution, is that if you have any wierd characters, they
arent escaped.

or you could just write a perl program

chris

-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: Monday, June 23, 2003 8:21 AM
To: O.S. Bos
Cc: [EMAIL PROTECTED]
Subject: Re: Textfile to a 2 column mysql database


Write a quick perl program.

O.S. Bos wrote:

>Hi there,
>
>I have a textfile that I want to get inserted into a database. The
>textfile consists of Questions and Answers. 1st line is a question. 2nd
>line of the textfile is the answer. And so on...
>
>What is the best way to import these lines into the database with 2
>columns. 1 column for the questions and one for the answers?
>
>Thx!
>Unox
>
>
>
>



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



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

How to UNSUBSCRIBE

[Christopher Knight]

I swear we went over this today...
for anybody still unsure...
give a look at the bottom of any email you recieve from the list and it will
say 'To unsubscribe' on the last line... just click the link

It will probably be at the end of this message even..


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

RE: RAID hardware suggestions/experience

[Christopher Knight]

Optimally, Yes, you should replace with exact same brand/model etc...
but you CAN replace with a different brand/ model drive of the same
amount of disk space or more.  It isn't recomended (because of 
different seek times, cache .. etc..) but if you are carefull and do
your research, you can get away with it w/o any adverse effects.

-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 17, 2003 12:33 PM
To: [EMAIL PROTECTED]
Subject: RE: RAID hardware suggestions/experience


> I have heard good thing about 3Ware, but I would suggest looking at the
> 8500-4 in combination with Western Digital's Raptor drive
> (http://www.tomshardware.com/storage/20030501/index.html). The Raptor is
> a 10,000 RPM SATA drive  which, combined with the 8500-4 SATA Raid card
> should give excellent performance at a great price. I would also look at
> Opteron based servers if you are looking for performance for a good
> price.
>
> Regards,
> Mike Hillyer
> www.vbmysql.com
>
>
> -Original Message-
> From: David Griffiths [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 17, 2003 10:59 AM
> To: [EMAIL PROTECTED]
> Subject: Re: RAID hardware suggestions/experience
>
>
> Anyone had any experience with 3Ware 7500-4 IDE RAID or the Promise
> SX-6000
> IDE RAID cards? Specifically for Linux. Heard bad things about Promise,
> good
> about 3Ware.
>
> David
> - Original Message -
> From: "Patrick Shoaf" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, June 17, 2003 9:40 AM
> Subject: Re: RAID hardware suggestions/experience
>
>
>> I am using 4 120G IDE Drives with an Adaptec IDE RAID Controller on
> RedHat
>> Linux providing 240G of RAID 5 storage.  While not quite as fast as
> SCSI,
> I
>> have found this to work very well.  You should be able to pickup a
> nice
>> dual processor XENON 2.4Ghz system w/1G Ram and IDE RAID loaded with
> RedHat
>> Linux ES for around $4,000.
>>
>> At 12:25 PM 6/17/2003, you wrote:
>> >Hi there,
>> >
>> >Our databank with all tables and idices is about 130GB big. The
> biggest
>> >limitations we encounter are on the I/O side.
>> >Therefore we are willing to update our data storage system to a RAID
> system
>> >(RAID 0+1, RAID 5, or RAID 10).
>> >
>> >Has anyone experience with such RAID systems?
>> >What should we buy?
>> > >From whom should we buy (We are located in New York City)?
>> >Do you have any experience you want to share?
>> >
>> >Thank you very much for your help and support!
>> >
>> >Bernd
>>
>>
>> Patrick J. Shoaf, Systems Engineer
>> [EMAIL PROTECTED]
>>
>> Midmon Internet Services, LLC
>> 100 Third Street
>> Charleroi, PA 15022
>> http://www.midmon.com
>> Phone: 724-483-2400 ext. 105
>>   or888-638-6963
>> Fax:   724-489-4386
>>
A significant question remains for SATA: basic drive reliability.  Related
to that is length of time drive will remain available.  A dirty secret of
RAID is that when a drive goes it must be replaced you must replace it
with the same drive (please..please tell me I'm wrong).  So, unless you
have a spare in the back you will end up replacing 3 drives (assuming Raid
5).  That may be why the WD model has such low capacity compared with the
normal IDE drives.

Just my 2 cents worth.

William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



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



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

RE: RAID hardware suggestions/experience

[Christopher Knight]

Im using a 3ware (which has great linux support) Escalade 7800 with 8
120GB/8MB cache ide drives in RAID 10 under Debian with 2.4.20 kernel.

I guess what we need to know is what platform and how much $$ you wanna
spend

-Original Message-
From: Bernd Jagla [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 17, 2003 11:26 AM
To: [EMAIL PROTECTED]; mysql
Subject: RAID hardware suggestions/experience


Hi there,

Our databank with all tables and idices is about 130GB big. The biggest
limitations we encounter are on the I/O side.
Therefore we are willing to update our data storage system to a RAID system
(RAID 0+1, RAID 5, or RAID 10).

Has anyone experience with such RAID systems?
What should we buy?
>From whom should we buy (We are located in New York City)?
Do you have any experience you want to share?

Thank you very much for your help and support!

Bernd




 =

 Please note that this e-mail and any files transmitted with it may be
 privileged, confidential, and protected from disclosure under
 applicable law. If the reader of this message is not the intended
 recipient, or an employee or agent responsible for delivering this
 message to the intended recipient, you are hereby notified that any
 reading, dissemination, distribution, copying, or other use of this
 communication or any of its attachments is strictly prohibited.  If
 you have received this communication in error, please notify the
 sender immediately by replying to this message and deleting this
 message, any attachments, and all copies and backups from your
 computer.



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



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

RE: delete record

[Christopher Knight]

hehe... been there...
if there isnt a way if you have logging turned on, you might be able to
find the insert and following updates statements in the log to rebuild the
row.

good luck
chris

-Original Message-
From: Arcangelo [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 10:20 AM
To: [EMAIL PROTECTED]
Subject: delete record


Hi,
I've done these operations:

- I deleted one record
- I didn't execute "optimize table"

Do you know if it's possible to undelete the record?
Many thanks Arcangelo




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

sum on counts

[Christopher Knight]

Can you sum on counts?
What I want is the # of objects with a freq of 1, freq of 2, freq of 3...
and then total # of objects.

>>select object_id from object_hist where type_id=5879;
+---+
| object_id |
+---+
|  2121 |
|  3234 |
|  2121 |
|  4876 |
|  4876 |
|  4876 |
|  4876 |
|  4876 |
|  4876 |
|  4876 |
|  4889 |
|  3091 |
|  3092 |
|  3092 |
|  3103 |
|  3103 |
|  1390 |
|  1874 |
|  3234 |
|  2121 |
|  4889 |
+---+

>>select object_id, count(*) from object_hist where type_id=5879 group by
object_id;
+---+--+
| object_id | count(*) |
+---+--+
|  1390 |1 |
|  1874 |1 |
|  2121 |3 |
|  3091 |1 |
|  3092 |2 |
|  3103 |2 |
|  3234 |2 |
|  4876 |7 |
|  4889 |2 |
+---+--+

What I want is the # of objects with a freq of 1, freq of 2, freq of 3...
and then total # of objects
...something like this... (in 1 row)

++++---+---+
| VIEW_1 | VIEW_2 | VIEW_3 | VIEW_MORE | TOTAL |
++++---+---+
|  3 |  4 |  1 | 1 | 9 |
+--+---+

I read this article http://www.devshed.com/Server_Side/MySQL/MySQLWiz/ and
got all excited and tried to
write something like this

>>select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2,
>>SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where
type_id=5879 group by object_id

and it complains about unknown column C.   Anyone have any insite on how to
do this?

Thanks
Chris


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

subselects in 4.1 and indexes

[Christopher Knight]

Im have a problem / misunderstanding with the 4.1 release.  Im having issues
with indexes related to 'IN' vs '=' in subselects.
assume the subselect returns 1 value

select SQL_NO_CACHE user_id from table_a where user_id in (select
SQL_NO_CACHE user_id from table_b where ... blah)
9.5 seconds...

select SQL_NO_CACHE user_id from table_a where user_id = (select
SQL_NO_CACHE user_id from table_b where ... blah)
0.0 seconds

explain select SQL_NO_CACHE user_id from table_a where user_id in (select
SQL_NO_CACHE user_id from table_b where ... blah)
... not using index...

explain select SQL_NO_CACHE user_id from table_a where user_id = (select
SQL_NO_CACHE user_id from table_b where ... blah)
.. using index on user_id

and the subselect take 0.0 seconds to run as well..


Does just using the IN operator prohibit table_a from using the user_id
index?

Thanks
Chris


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