Re: a newserver would be better than this mailing list , 1 moretime

2002-08-01 Thread David Wolf 

Personally, I think one simple change would work wonders for the list: 
adding a [mysql] ident at the beginning of each subject line. An EASY 
change that would allow for MUCH easier sorting of the list into a 
different folder. From there, you can thread it with (almost) any modern 
mailreader..

David Wolf

p.s. PLEASE can you put a [mysql] tag on the list???



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Query help

2001-12-24 Thread David Wolf 

I need to replace some data with data from a backup. Here's the scenerio...

I have 2 databases. a and abak

a and abak are the same (but, ones a few hours old with the right data ;) )

a and abak have a table, posts and a field 'attach'. I'd like to take 
the data from abak.posts.attach and place it (overwriting where needed) 
into a.posts.attach where posts.attach != ''

Can someone help me? I'm not quite sure what would do it properly..

Thanks!
David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQLGUI hangs on certain commands

2001-11-03 Thread David Wolf 

Yes.. It is the windows version of MySQLGUI that I am using. Windows 2000
SP2

David

- Original Message -
From: Sinisa Milivojevic [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, November 03, 2001 4:14 AM
Subject: Re: MySQLGUI hangs on certain commands


 [EMAIL PROTECTED] writes:
  MySQL is working perfectly on my Linux box. I downloaded MySQLGUI, and
ran it.
  It connects fine, I can view a table, etc.. But, when I click on the
  GRANT/REVOKE menu, it hangs on the Windows side. When I go to the Linux
side and
  issue SHOW PROCESSLIST, there are as many processes connected as I have
ports
  open. When I forcibly kill the MySQLGUI, and issue SHOW PROCESSLIST,
it's back
  to 'normal'.
 
  The above happens as SOON as a I click the GRANT/REVOKE menu item! I can
view
  the mysql.user table (by using the view table button), but, it dies when
I go to
  the GRANT/REVOKE menu item.
 
  David
 

 Hi!

 If I understand you well, you are talking about Windows version of
 MySQLGUI ??

 Please clear it out.

 --
 Regards,
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED]
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Fulltime Developer
 /_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
___/   www.mysql.com


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Strange results from query

2001-10-30 Thread David Wolf 

I'm using the following query

SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice
  FROM archive a, archive b
WHERE a.status='s' AND a.addrdsp IS NOT NULL
  AND a.addrdsp = b.addrdsp AND a.solddate  b.solddate
  AND date_add(a.solddate, interval 1 year)  b.solddate
ORDER BY a.addrdsp, a.solddate
LIMIT 200;

I'm trying to look at about 300,000 rows of property data. I'm interested in
knowing which properties have been sold 2 or more times within a 1 year
period.

However, when I run the query, I get 100 Brazeau Cresc SW and 100 Bridlewood
Road SW in my list of properties--even though they have not had 2 or more
'sales' within a period of one year.  I need to limit those properties which
only occur once in the table.

Also, even though all of the fields are indexed, the query takes up to 12
minutes to complete!

Thanks

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Optimizing query (2nd attempt)

2001-10-29 Thread David Wolf 

Maybe I'm missing something here--I don't know of a way to create an index
on TWO tables at once? Also, when I do:

EXPLAIN SELECT log.entity, log.action,
  LEFT(users.username,10) AS username,
  LEFT(boards.title,15) AS Board,
  LEFT(topics.subject,22) as Subject,
  log.postid, log.extraid,
  LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
  LEFT JOIN boards ON log.boardid=boards.id
  LEFT JOIN topics ON log.topicid = topics.id
WHERE users.username=testuser;

(users.username is indexed), I get the following:

+++---+-+-+-+---
--++
| table  | type   | possible_keys | key | key_len | ref | rows
| Extra  |
+++---+-+-+-+---
--++
| log| ALL| NULL  | NULL|NULL | NULL|
1199187 ||
| users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  |
1 | where used |
| boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId |
1 ||
| topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId |
1 ||
+++---+-+-+-+---
--++
4 rows in set (0.00 sec)

It's just simply not using the index on users.. Did I miss something?

David

- Original Message -
From: Bill Adams [EMAIL PROTECTED]
To: David Wolf [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, October 29, 2001 9:21 AM
Subject: Re: Optimizing query (2nd attempt)


 David Wolf wrote:

 Not quite fixed.. When I run the query without limiting by time, it still
 fails to use the userid key. i.e. if I only select where
 users.username=testuser, I'd expect that users.username to return the
 users.id=2, and to search using the indexed log.userid=2


 MySQL can only use one index on a table at a time.  It also uses the
columns in
 the order in which they are defined.  ORDER MATTERS!

 The manual does not seem to cover this, but at least Informix will stop
using
 an index when an inequality is hit.  E.g.: if you have an index on (a, b,
c )
 and the query has WHERE a=5 AND b2 AND c=10, the only part of the index
that
 will be used is (a, b).  (Monty  co, is this true with MySQL? Can you add
 something to the manual either way?)

 So assuming this is true in your where clause:

  WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
AND users.username=testuser;

 If you have an index on ( logTime, username), since you have an inequality
for
 lotTime in the query, username will NOT be used.  However if you have the
index
 on (username, logTime)  --or even just the first 10 chars or so of
username +
 logTime-- then both username AND logTime will be used in the index.

 You may want to try this to see if it makes any difference.

 And, of course, run myisamchk -a on the tables after you build indexes.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQLGUI hangs on certain commands

2001-10-28 Thread David Wolf 

I've been trying to play with MySQLGUI lately (running the win32 version of
the GUI, Linux version of the server). I can connect fine. Run queries fine.
But, when I try to do any grants on a database or edit a table, the client
connects to the server as many times as it can (before the server runs out
of connections available) and hangs until I forcibly kill it. Does anyone
know why that would happen??

Thanks,

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Optimizing query (2nd attempt)

2001-10-28 Thread David Wolf 

I have a query as follows:

SELECT log.entity, log.action,
  LEFT(users.username,10) AS username,
  LEFT(boards.title,15) AS Board,
  LEFT(topics.subject,22) as Subject,
  log.postid, log.extraid,
  LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
  LEFT JOIN boards ON log.boardid=boards.id
  LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
  AND users.username=testuser;

When I run an 'explain' on it, I get:

+++---+-+-+-+---
--++
| table  | type   | possible_keys | key | key_len | ref | rows
| Extra  |
+++---+-+-+-+---
--++
| log| ALL| time  | NULL|NULL | NULL|
1192384 | where used |
| users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  |
1 | where used |
| boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId |
1 ||
| topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId |
1 ||
+++---+-+-+-+---
--++
4 rows in set (0.01 sec)

Now.. If I exclude the 'users.username=testuser' and substitute it for the
userid that I got in a previous query (i.e. userid=2) so that the query
becomes:

SELECT log.entity, log.action,
  LEFT(users.username,10) AS username,
  LEFT(boards.title,15) AS Board,
  LEFT(topics.subject,22) as Subject,
  log.postid, log.extraid,
  LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
  LEFT JOIN boards ON log.boardid=boards.id
  LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
  AND log.userid=2;


 and run an explain, I get...

+++---+-+-+-+---
++
| table  | type   | possible_keys | key | key_len | ref | rows
| Extra  |
+++---+-+-+-+---
++
| log| ref| time,userid   | userid  |   4 | const   | 27198
| where used |
| users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  | 1
||
| boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId | 1
||
| topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId | 1
||
+++---+-+-+-+---
++
4 rows in set (0.00 sec)

Big difference from 1.19million rows to 27198 rows... My question is this.
How can I optimize the query with the left joins so that the optimizer will
first grab the userid from the username and then use the userid index on log
to return the results fast?

Thanks in advance,

David




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Optimizing query (2nd attempt)

2001-10-28 Thread David Wolf 

Yes.. There is an index on users.username :)

David

- Original Message -
From: Tore Van Grembergen [EMAIL PROTECTED]
To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, October 28, 2001 8:46 AM
Subject: Re: Optimizing query (2nd attempt)


 do you have an index defined on users.username ?


 - Original Message -
 From: David Wolf [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Sunday, October 28, 2001 4:26 PM
 Subject: Optimizing query (2nd attempt)


  I have a query as follows:
 
  SELECT log.entity, log.action,
LEFT(users.username,10) AS username,
LEFT(boards.title,15) AS Board,
LEFT(topics.subject,22) as Subject,
log.postid, log.extraid,
LEFT(from_unixtime(log.logtime),19) AS time, log.ip
  FROM log LEFT JOIN users ON log.userid = users.id
LEFT JOIN boards ON log.boardid=boards.id
LEFT JOIN topics ON log.topicid = topics.id
  WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
AND users.username=testuser;
 
  When I run an 'explain' on it, I get:
 
 

+++---+-+-+-+---
  --++
  | table  | type   | possible_keys | key | key_len | ref |
rows
  | Extra  |
 

+++---+-+-+-+---
  --++
  | log| ALL| time  | NULL|NULL | NULL|
  1192384 | where used |
  | users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  |
  1 | where used |
  | boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId |
  1 ||
  | topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId |
  1 ||
 

+++---+-+-+-+---
  --++
  4 rows in set (0.01 sec)
 
  Now.. If I exclude the 'users.username=testuser' and substitute it for
 the
  userid that I got in a previous query (i.e. userid=2) so that the query
  becomes:
 
  SELECT log.entity, log.action,
LEFT(users.username,10) AS username,
LEFT(boards.title,15) AS Board,
LEFT(topics.subject,22) as Subject,
log.postid, log.extraid,
LEFT(from_unixtime(log.logtime),19) AS time, log.ip
  FROM log LEFT JOIN users ON log.userid = users.id
LEFT JOIN boards ON log.boardid=boards.id
LEFT JOIN topics ON log.topicid = topics.id
  WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
AND log.userid=2;
 
 
   and run an explain, I get...
 
 

+++---+-+-+-+---
  ++
  | table  | type   | possible_keys | key | key_len | ref |
rows
  | Extra  |
 

+++---+-+-+-+---
  ++
  | log| ref| time,userid   | userid  |   4 | const   |
 27198
  | where used |
  | users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  |
 1
  ||
  | boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId |
 1
  ||
  | topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId |
 1
  ||
 

+++---+-+-+-+---
  ++
  4 rows in set (0.00 sec)
 
  Big difference from 1.19million rows to 27198 rows... My question is
this.
  How can I optimize the query with the left joins so that the optimizer
 will
  first grab the userid from the username and then use the userid index on
 log
  to return the results fast?
 
  Thanks in advance,
 
  David
 
 
 
 
  -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
 
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail
[EMAIL PROTECTED]
  Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Optimizing query (2nd attempt)

2001-10-28 Thread David Wolf 

How do you do a compound index to index between two tables?

David

- Original Message - 
From: Tore Van Grembergen [EMAIL PROTECTED]
To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, October 28, 2001 8:57 AM
Subject: Re: Optimizing query (2nd attempt)


 maybe you heva to declare a compound index with userid and username.
 the sql parser now does not use the index on username.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Optimizing query (2nd attempt)

2001-10-28 Thread David Wolf 

I seem to have fixed it.. I ran myisamchk on all the tables--and now the
indexes work as expected.. Very strange indeed.. Though, there is a strange
twist now: explain reports fewer rows to be checked than are displayed with
the query runs.. Is that normal?

David

- Original Message -
From: Tore Van Grembergen [EMAIL PROTECTED]
To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, October 28, 2001 8:57 AM
Subject: Re: Optimizing query (2nd attempt)


 maybe you heva to declare a compound index with userid and username.
 the sql parser now does not use the index on username.

 - Original Message -
 From: David Wolf [EMAIL PROTECTED]
 To: Tore Van Grembergen [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Sent: Sunday, October 28, 2001 4:46 PM
 Subject: Re: Optimizing query (2nd attempt)


  Yes.. There is an index on users.username :)
 
  David
 
  - Original Message -
  From: Tore Van Grembergen [EMAIL PROTECTED]
  To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED]
  Sent: Sunday, October 28, 2001 8:46 AM
  Subject: Re: Optimizing query (2nd attempt)
 
 
   do you have an index defined on users.username ?
  
  
   - Original Message -
   From: David Wolf [EMAIL PROTECTED]
   To: [EMAIL PROTECTED]
   Sent: Sunday, October 28, 2001 4:26 PM
   Subject: Optimizing query (2nd attempt)
  
  
I have a query as follows:
   
SELECT log.entity, log.action,
  LEFT(users.username,10) AS username,
  LEFT(boards.title,15) AS Board,
  LEFT(topics.subject,22) as Subject,
  log.postid, log.extraid,
  LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
  LEFT JOIN boards ON log.boardid=boards.id
  LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
  AND users.username=testuser;
   
When I run an 'explain' on it, I get:
   
   
  
 

+++---+-+-+-+---
--++
| table  | type   | possible_keys | key | key_len | ref
|
  rows
| Extra  |
   
  
 

+++---+-+-+-+---
--++
| log| ALL| time  | NULL|NULL | NULL
|
1192384 | where used |
| users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId
|
1 | where used |
| boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId
|
1 ||
| topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId
|
1 ||
   
  
 

+++---+-+-+-+---
--++
4 rows in set (0.01 sec)
   
Now.. If I exclude the 'users.username=testuser' and substitute it
 for
   the
userid that I got in a previous query (i.e. userid=2) so that the
 query
becomes:
   
SELECT log.entity, log.action,
  LEFT(users.username,10) AS username,
  LEFT(boards.title,15) AS Board,
  LEFT(topics.subject,22) as Subject,
  log.postid, log.extraid,
  LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
  LEFT JOIN boards ON log.boardid=boards.id
  LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
  AND log.userid=2;
   
   
 and run an explain, I get...
   
   
  
 

+++---+-+-+-+---
++
| table  | type   | possible_keys | key | key_len | ref
|
  rows
| Extra  |
   
  
 

+++---+-+-+-+---
++
| log| ref| time,userid   | userid  |   4 | const
|
   27198
| where used |
| users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId
|
   1
||
| boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId
|
   1
||
| topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId
|
   1
||
   
  
 

+++---+-+-+-+---

++
4 rows in set (0.00 sec)
   
Big difference from 1.19million rows to 27198 rows... My question is
  this.
How can I optimize the query with the left joins so that the
optimizer
   will
first grab the userid from the username and then use the userid
index
 on
   log
to return the results fast?
   
Thanks in advance,
   
David
   
   
   
   
  
 -
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)
   
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
  [EMAIL PROTECTED]
Trouble unsubscribing? Try:
http

Re: Optimizing query (2nd attempt)

2001-10-28 Thread David Wolf 

Not quite fixed.. When I run the query without limiting by time, it still
fails to use the userid key. i.e. if I only select where
users.username=testuser, I'd expect that users.username to return the
users.id=2, and to search using the indexed log.userid=2

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Optimizing queries

2001-10-26 Thread David Wolf 

I have a query as follows:

SELECT log.entity, log.action,
  LEFT(users.username,10) AS username,
  LEFT(boards.title,15) AS Board,
  LEFT(topics.subject,22) as Subject,
  log.postid, log.extraid,
  LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
  LEFT JOIN boards ON log.boardid=boards.id
  LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
  AND users.username=testuser;

When I run an 'explain' on it, I get:

+++---+-+-+-+---
--++
| table  | type   | possible_keys | key | key_len | ref | rows
| Extra  |
+++---+-+-+-+---
--++
| log| ALL| time  | NULL|NULL | NULL|
1192384 | where used |
| users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  |
1 | where used |
| boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId |
1 ||
| topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId |
1 ||
+++---+-+-+-+---
--++
4 rows in set (0.01 sec)

Now.. If I exclude the 'users.username=testuser' and substitute it for the
userid that I got in a previous query (i.e. userid=2) so that the query
becomes:

SELECT log.entity, log.action,
  LEFT(users.username,10) AS username,
  LEFT(boards.title,15) AS Board,
  LEFT(topics.subject,22) as Subject,
  log.postid, log.extraid,
  LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
  LEFT JOIN boards ON log.boardid=boards.id
  LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime  UNIX_TIMESTAMP(2000-10-26 23:00:00)
  AND log.userid=2;


 and run an explain, I get...

+++---+-+-+-+---
++
| table  | type   | possible_keys | key | key_len | ref | rows
| Extra  |
+++---+-+-+-+---
++
| log| ref| time,userid   | userid  |   4 | const   | 27198
| where used |
| users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  | 1
||
| boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId | 1
||
| topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId | 1
||
+++---+-+-+-+---
++
4 rows in set (0.00 sec)

Big difference from 1.19million rows to 27198 rows... My question is this.
How can I optimize the query with the left joins so that the optimizer will
first grab the userid from the username and then use the userid index on log
to return the results fast?

Thanks in advance,

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Query help...

2001-10-24 Thread David Wolf 

I'm trying to come up with a query to do the following... I'm not having
lots of luck :(

The table is a user database. The columns I'm interested in are: username,
lastip. I'm interested in pulling information out that would show usernames
for each IP that appears more than once in the database.. i.e.

usernameip
persona1.1.1.1
personb1.2.3.4
personc1.1.1.1

I'd be interested in seeing persona and personc (both have 1.1.1.1 ip's)

I've tried..

SELECT lastip,count(*) FROM users GROUP BY lastip;

but that only gives me an unordered list of the # of times an IP is used..

SELECT distinct(count(*)) FROM users GROUP BY lastip;

but that doesn't join the info -- nor give me the ip's -- just frequency
(when I add 'lastip' to the select I get an error)

I'm thinking that I'm going about this the wrong way.. But, I can't quite
get a clue.

Can anyone help me out here?

Thanks!

David Wolf



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query help...

2001-10-24 Thread David Wolf 

Not quite what I need.. Though, it's very close :)  The problem is that with
the query given, it only shows one username for each IP.. I actually want to
show ALL usernames for each IP with the IP occurs more than once... (and
ignore the users who have a distinct IP address).

David

- Original Message -
From: David Hugh-Jones [EMAIL PROTECTED]
To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, October 24, 2001 6:19 PM
Subject: Re: Query help...



 Try
 SELECT lastip,username FROM users GROUP BY lastip HAVING count(*)  1;



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query help...

2001-10-24 Thread David Wolf 

It's still not quite doing what I want. I only want a list of IP's where
there are more than 1 instance of an ip--and then display each of the
multiple occurrences of the single ip (and repeat for each ip which has more
than 1 occurrence)..

The queries are all so close, but, don't give what I want :(

David

- Original Message -
From: Steve Meyers [EMAIL PROTECTED]
To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, October 24, 2001 2:38 PM
Subject: RE: Query help...


I think you're looking for:

SELECT username, ip, count(*) FROM users GROUP BY 1, 2

Steve Meyers


 -Original Message-
 From: David Wolf [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, October 24, 2001 12:21 PM
 To: [EMAIL PROTECTED]
 Subject: Query help...


 I'm trying to come up with a query to do the following... I'm not having
 lots of luck :(

 The table is a user database. The columns I'm interested in are: username,
 lastip. I'm interested in pulling information out that would show
 usernames
 for each IP that appears more than once in the database.. i.e.

 usernameip
 persona1.1.1.1
 personb1.2.3.4
 personc1.1.1.1

 I'd be interested in seeing persona and personc (both have 1.1.1.1 ip's)

 I've tried..

 SELECT lastip,count(*) FROM users GROUP BY lastip;

 but that only gives me an unordered list of the # of times an IP is used..

 SELECT distinct(count(*)) FROM users GROUP BY lastip;

 but that doesn't join the info -- nor give me the ip's -- just frequency
 (when I add 'lastip' to the select I get an error)

 I'm thinking that I'm going about this the wrong way.. But, I can't quite
 get a clue.

 Can anyone help me out here?

 Thanks!

 David Wolf



 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query help...

2001-10-24 Thread David Wolf 

Thanks!! Worked like a dream! I'm not quite sure why it knew to pull only
ip's that are in there more than once though?

David

- Original Message -
From: Steve Meyers [EMAIL PROTECTED]
To: David Wolf [EMAIL PROTECTED]; Steve Meyers
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, October 24, 2001 3:46 PM
Subject: RE: Query help...


I think I understand.  This should work...

select distinct a.username, a.ip from users a, users b where a.ip=b.ip 
a.username != b.username;

Steve Meyers


 -Original Message-
 From: David Wolf [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, October 24, 2001 3:17 PM
 To: Steve Meyers; [EMAIL PROTECTED]
 Subject: Re: Query help...


 It's still not quite doing what I want. I only want a list of IP's where
 there are more than 1 instance of an ip--and then display each of the
 multiple occurrences of the single ip (and repeat for each ip
 which has more
 than 1 occurrence)..

 The queries are all so close, but, don't give what I want :(

 David

 - Original Message -
 From: Steve Meyers [EMAIL PROTECTED]
 To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Sent: Wednesday, October 24, 2001 2:38 PM
 Subject: RE: Query help...


 I think you're looking for:

 SELECT username, ip, count(*) FROM users GROUP BY 1, 2

 Steve Meyers


  -Original Message-
  From: David Wolf [mailto:[EMAIL PROTECTED]]
  Sent: Wednesday, October 24, 2001 12:21 PM
  To: [EMAIL PROTECTED]
  Subject: Query help...
 
 
  I'm trying to come up with a query to do the following... I'm not having
  lots of luck :(
 
  The table is a user database. The columns I'm interested in
 are: username,
  lastip. I'm interested in pulling information out that would show
  usernames
  for each IP that appears more than once in the database.. i.e.
 
  usernameip
  persona1.1.1.1
  personb1.2.3.4
  personc1.1.1.1
 
  I'd be interested in seeing persona and personc (both have 1.1.1.1 ip's)
 
  I've tried..
 
  SELECT lastip,count(*) FROM users GROUP BY lastip;
 
  but that only gives me an unordered list of the # of times an
 IP is used..
 
  SELECT distinct(count(*)) FROM users GROUP BY lastip;
 
  but that doesn't join the info -- nor give me the ip's -- just frequency
  (when I add 'lastip' to the select I get an error)
 
  I'm thinking that I'm going about this the wrong way.. But, I
 can't quite
  get a clue.
 
  Can anyone help me out here?
 
  Thanks!
 
  David Wolf
 
 
 
  -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
 
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail
  [EMAIL PROTECTED]
  Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 



 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php







-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL for Dummies (newbies)?

2001-10-24 Thread David Wolf 

I actually find the command line databases way easier to use than graphical
ones. You have more direct control over them, and they are leaner/faster
than GUI based ones. However, there are GUI based admin tools that you can
get from the MySQL website so you can feel more 'at home'.

I was a *real* SQL neophyte a few months ago (well, closer to a year now).
I'm not perfect, and I'm still learning, but, I can normally get any query
right within a few tries. One of the absolute best books I've read is a book
called MySQL by Paul DuBois (published New Riders).

That's probably the best place to start...

David

- Original Message -
From: Todd Williamsen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 24, 2001 6:16 PM
Subject: MySQL for Dummies (newbies)?


 I come from a microsoft environment and never really had to deal with a
 command line based database before.

 All the documentation for MySQL doesn't make sense to a rookie and is
 frustrating... Anywhere to go where I can feel like I can learn
 something?

 Thank you,

 Todd Williamsen, MCSE
 home: 847.265.4692
 Cell: 847.867.9427


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php