Hi,
I asked this question over on stackoverflow - basically I have a query
and when using EXPLAIN I see that around 2400 rows are still being
scanned. I'd added various indexes but it cannot make it perform any
better.
I would appreciate if anyone has any further ideas?
mysql -utim
Then
mysql SELECT USER(),CURRENT_USER();
Login as root and:
delete from mysql.user where user='';
And try again with tim with password.
Thanks
Claudio
On Oct 19, 2011 7:47 AM, Johan De Meersman vegiv...@tuxera.be wrote:
Try not passing the password and typing it at the prompt. If
Am 19.10.2011 01:36, schrieb Tim Johnson:
try mysql -u tim -p
Same error:
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
password: NO)
and enter yur password in the followed dialog
Doesn't even ask for the pwd..
then your mysql CLIENT is broken or somehow
On Wed, Oct 19, 2011 at 05:09:13PM +1100, Wayne W wrote:
Hi,
I asked this question over on stackoverflow - basically I have a query
and when using EXPLAIN I see that around 2400 rows are still being
scanned. I'd added various indexes but it cannot make it perform any
better.
I would
- Original Message -
From: Sergei Petrunia pser...@askmonty.org
Create index on (importance, company_id) (or
(company_id,importance), should
not matter). When that index is present, the query should use ref
access on it using both key parts (if by some crazy reason it doesn't, use
mysql select * from beers;
++---++
| ID | name | colour |
++---++
| 1 | carlsburg | 2 |
| 2 | tuburg| 1 |
| 3 | tuburg| 9 |
++---++
3 rows in set (0.00 sec)
mysql select * from colours;
+++
| id
On Wed, Oct 19, 2011 at 16:33, Michael Dykman mdyk...@gmail.com wrote:
Try this. I sometime get wierd results when I fail to use aliases in a
join. Also, the parentheses are required.
- md
select * from beers b inner join colours c on (b.colour = c.ID);
Thank you Michael. That does work,
* Reindl Harald h.rei...@thelounge.net [111018 23:24]:
Am 19.10.2011 01:36, schrieb Tim Johnson:
try mysql -u tim -p
Same error:
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
password: NO)
and enter yur password in the followed dialog
Doesn't even ask for
Hello Dotan,
On 10/19/2011 09:57, Dotan Cohen wrote:
mysql select * from beers;
++---++
| ID | name | colour |
++---++
| 1 | carlsburg | 2 |
| 2 | tuburg| 1 |
| 3 | tuburg| 9 |
++---++
3 rows in set
Am 19.10.2011 17:45, schrieb Tim Johnson:
* Reindl Harald h.rei...@thelounge.net [111018 23:24]:
Am 19.10.2011 01:36, schrieb Tim Johnson:
try mysql -u tim -p
Same error:
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
password: NO)
and enter yur password in the
On Wed, Oct 19, 2011 at 18:00, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
This is a simple misunderstanding. From the page you quote, the syntax
patterns for an OUTER join are these:
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference
Assuming a table such this:
| ID | messageID | userID |
||-||
| 1 | 345 | 71 |
| 2 | 984 | 71 |
| 3 | 461 | 72 |
| 4 | 156 | 73 |
| 5 | 441 | 73 |
| 6 | 489 | 73 |
| 7 | 483 | 74 |
While we have him online, I think we could all take a moment and be grateful
for the contributions of Shawn Green.
When I see the Oracle-bashing on this list, I am often reminded that we
still have a hard-core MySQL developer who has survived the ride to Sun and
again to Oracle who is still
I'm afraid that what you are looking for simply cannot be done with MySQL
alone. You will need to pare your results at the application layer.
Remember that rows have no inherent order except for conforming to any
ORDER BY clause contained within the query.
- md
On Wed, Oct 19, 2011 at 1:27
On Wed, Oct 19, 2011 at 12:29 PM, Michael Dykman mdyk...@gmail.com wrote:
While we have him online, I think we could all take a moment and be
grateful
for the contributions of Shawn Green.
When I see the Oracle-bashing on this list, I am often reminded that we
still have a hard-core MySQL
You could do a GROUP_CONCAT to get you close:
SELECT userID, SUBSTRING_INDEX(GROUP_CONCAT(messageID), ',', 1) messageList
FROM table
GROUP BY userID
| userID | messageList |
|--|---|
| 71| 984|
| 73| 441, 489|
| 74|
Thanks Shawn!
Ninus from Montreal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
On 10/19/2011 13:19, Dotan Cohen wrote:
...
Thank you Shawn! I see that I am getting support right from the top!
So far as I understand, an outer join should return all matched and
unmatched rows (essentially all rows) from both tables. So it is not
clear to me what is the difference between a
Can someone tell me if what I'm trying to do can be done more efficiently? I
just got off the phone with Rackspace when my server was hung up, and they
found a whole bunch of this one same query was all stacked up with waiting
queries and locked.
Here's the query:
$query = insert ignore into
On 10/19/2011 13:29, Michael Dykman wrote:
While we have him online, I think we could all take a moment and be grateful
for the contributions of Shawn Green.
When I see the Oracle-bashing on this list, I am often reminded that we
still have a hard-core MySQL developer who has survived the ride
On Tue, October 18, 2011 22:40, Johan De Meersman wrote:
- Original Message -
From: Ryan Mark rm...@tribune.com
(WordPress does not like to share an app server) and added memcache.
Really? We run dozens of the thing on a couple of virtuals with no
problems at all. Then again, we
- Original Message -
From: Brian Dunning br...@briandunning.com
Can someone tell me if what I'm trying to do can be done more
efficiently? I just got off the phone with Rackspace when my server
was hung up, and they found a whole bunch of this one same query was
all stacked up with
- Original Message -
From: Wm Mussatto mussa...@csz.com
I've turned on the reporting of full table scans into the show query log
and Wordpress has a large number of full table scans so it could easily be
Never trust your vendor to know their way around your systems. Check where
For me given the sample data the following worked.
The inner select says find all first messages, the outer says give me all
messages that are thus not first messages.
select * from table1 where messageID NOT IN (
select messageID from table1
group by userID
)
Some times just playing with the
Ah-hah! :)
Actually, I did something similar to that a month or so ago. I ran into a speed
limitation on a not-small database (~3mill rows). So be careful.
Luckily in my case, I put all the 'minimum' ids in a memory table with an index
and it solved it. It also was a cleanup script, and not
Can you rotate that table daily, thus keeping it small?
You can then move yesterdays data somewhere for post processing.
try an insert if it fails generate the new table from the template, done.
Thus no checking other then once a day when the insert fails.
On Wed, Oct 19, 2011 at 2:54 PM, Johan
I also want to say Thank you Shawn for your valuable contribution. On the
note on Resumes -I thought I would put it out here. My company is looking
for an excellent MySQL DBA (Oracle and MSSQL experience a plus) Please send
me your resume.
Thanks!
Sabika
On Wed, Oct 19, 2011 at 12:30 PM, Shawn
On Wed, Oct 19, 2011 at 21:10, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
What you are describing is a FULL OUTER JOIN. This is not supported, yet, in
MySQL. We only support INNER, NATURAL, LEFT, and RIGHT.
To simulate a FULL OUTER JOIN, you need to construct a UNION of a LEFT and a
On Thu, Oct 20, 2011 at 00:06, Basil Daoust bdao...@lemonfree.com wrote:
For me given the sample data the following worked.
The inner select says find all first messages, the outer says give me all
messages that are thus not first messages.
select * from table1 where messageID NOT IN (
On Thu, Oct 20, 2011 at 00:11, Derek Downey de...@orange-pants.com wrote:
Ah-hah! :)
Actually, I did something similar to that a month or so ago. I ran into a
speed limitation on a not-small database (~3mill rows). So be careful.
Luckily in my case, I put all the 'minimum' ids in a memory
; 2011/10/19 17:00 +0200, Dotan Cohen
mysql select * from beers b outer join colours c on (b.colour = c.ID);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'outer join colours c on (b.colour
I made this query a view, called MEMBERP, no problem:
SELECT MemberID, ereStart, DateModified, MembershipExpires, MemberSince,
Category, Boardster, GROUP_CONCAT(DISTINCT Salutation ORDER BY Rank) AS
Salutation, GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS GivenName,
GROUP_CONCAT(DISTINCT
32 matches
Mail list logo
