Re: A simple 2 table query gets very slow when using ORDER BY

One thing I don't understand is what MySQL tried to do first. Why did MySQL chose co.name as a key? On 3/3/07, Filip Krejci <[EMAIL PROTECTED]> wrote: > SELECT * > FROM employees2skills e2s FORCE INDEX (mod_time ) > INNER JOIN companies c ON c.id = e2s.co_id > ORDER BY e2s.mod_time limit 5; Yes

Re: Need sql optimization help

Sorry for the typo, lose the MAX! ... SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,'{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value, e.triggerid, s.color FROM fs_events e JOIN hosts h USING (hostid) JOIN fs_severity s ON (e.severity = s.severityid) LEFT

Re: [MySQL] Re: Can't Bind to Port

- Original Message From: Ashley M. Kirchner <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Saturday, March 3, 2007 4:09:41 PM Subject: Re: [MySQL] Re: Can't Bind to Port >This indicates that you did indeed start the daemon. >So then why are you trying to start it again? Of course i

Re: Need sql optimization help

Your inner query guarantees that MySQL will have to test an awfull lot of combinations: (# of records in fs_events)^2 * (# of records in hosts) * (# of records in severity) (SELECT max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid) needs to be recalculated every time we try to match

how to optimize this simple query with join?

I have to tables (on mysql 5.0.22): Table: shelf CREATE TABLE `shelf` ( `isbn` varchar(10) NOT NULL default '', `product_type` char(1) default NULL, `title` varchar(150) NOT NULL default '', (...) PRIMARY KEY (`isbn`), KEY `publ_date` (`publ_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

RE: Need sql optimization help

Gives me invalid use of group function: SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,'{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value, e.triggerid, s.color FROM fs_events e JOIN hosts h USING (hostid) JOIN fs_severity s ON (e.severity = s.sev

Re: [MySQL] Re: Can't Bind to Port

Drew Jenkins wrote: server312# /usr/local/bin/mysqld_safe & [1] 801 server312# Starting mysqld daemon with databases from /var/db/mysql server312# ps wax | grep mysqld 801 p0 S 0:00.01 /bin/sh /usr/local/bin/mysqld_safe 849 p0 S 0:00.16 /usr/local/libexec/mysqld --basedir=/usr/

Re: Can't Bind to Port

20- Original Message From: ed <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 12:16:42 PM Subject: Re: Can't Bind to Port I figured it wouldn't hurt anything to change permissions on files, so I went ahead and changed all the permissions everywhere.

Re: Need sql optimization help

Bryan, A 'Not Exists' query is usually faster when coded as an exclusion join, eg for max-some-value per key, left join on the key variable and left.value < right. value where left.key is null, so you would need something like ... SELE

Need sql optimization help

I have the following sql that works for what I want to see but is terribly slow due to the subquery. Any suggestions on how to get same result faster? I have tried group by and cant seem to get the correct results that way Also is there a product that can help optimize sql and indexing? SELEC

Re: Can't Bind to Port

- Original Message From: Tommy Nordgren <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 1:39:14 PM Subject: Re: Can't Bind to Port Well, I don't know what you do with this one, Tommy. I just cut and pasted your command, but when I hit the up arrow t

Re: Can't Bind to Port

- Original Message From: ed <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 1:06:16 PM Subject: Re: Can't Bind to Port None of those directories existed on my system. So I decided to try and find the ones to which you referred. The matches were inex

Re: Can't Bind to Port

- Original Message From: Tommy Nordgren <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 1:03:30 PM Subject: Re: Can't Bind to Port >You are probably using another shell than bash or another sh variant. No, I'm using bash...the standard shell. Did

Re: Can't Bind to Port

- Original Message From: Tommy Nordgren <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Saturday, March 3, 2007 12:34:37 PM Subject: Re: Can't Bind to Port >There is no need to be superuser to run find. But there can be problems >if there is anoth

RE: Best update strategy with MyISAM

At 11:18 AM 3/2/2007, Gary W. Smith wrote: Mike, Below is an message written a few months back tailing a discussion on why MyISAM is and is not faster than InnoDB. Basically my understanding is that if you have multiple simultaneous updates/inserts, stay away from MyISAM. We switched from MyIS

Re: Can't Bind to Port

On 3 mar 2007, at 15.31, Drew Jenkins wrote: - Original Message From: ed <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 10:24:54 AM Subject: Re: Can't Bind to Port be root/superuser; try it as "find / -name mysql" and then, (as in 2nd) send the

Re: Can't Bind to Port

- Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 12:03:40 PM Subject: Re: Can't Bind to Port >At this point, make >certain that the mysql instance that you started (pid 801) is shut down >properly, then pro

Re: Can't Bind to Port

- Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 11:25:37 AM Subject: Re: Can't Bind to Port I rebooted, then... server312# ps wax|grep mysqld server312# /usr/local/bin/mysqld_safe & [1] 801 server312# Sta

Re: Database table with unique no duplications

Maybe I have understood the requirement incorrectly. here is what I think the author wants: He has a table with 3 fields: table1 col1, col2, col3 Now when a row is inserted he wants to ensure that 2 fields are unique: (col1 & col2) OR (col2 & col3) OR (col1 & col3). So - to understand the requi

innodb crashing

Mysql 5.0.33 on FreeBSD 6.2 amd64 platform. Using stock my-innodb-heavy-4G.cnf, with just the following changes max_connections = 250 innodb_buffer_pool_size = 8G innodb_log_file_size = 1024M innodb_data_file_path = /var/db/mysql/tblgrp1/ibdata1:4096M;/var/db/mysql/tblgrp1/ibdata2:4096M;/var/db/

Re: Can't Bind to Port

26- Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 10:53:45 AM Subject: Re: Can't Bind to Port >kill off the first instance, the port will/should free up and then you >can start your [second] instance. # p

Re: Can't Bind to Port

- Original Message From: ed <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Sent: Saturday, March 3, 2007 10:24:54 AM Subject: Re: Can't Bind to Port >be root/superuser; >try it as "find / -name mysql" and then, (as in 2nd) send they instances >of mysql that you do not want to /

Re: A simple 2 table query gets very slow when using ORDER BY

SELECT * FROM employees2skills e2s FORCE INDEX (mod_time ) INNER JOIN companies c ON c.id = e2s.co_id ORDER BY e2s.mod_time limit 5; Yes, this query will be probably the best. This go through mod_time index (which is already sorted) from begin and try to join companies. When row count achieve

Re: Can't Bind to Port

- Original Message From: Tommy Nordgren <[EMAIL PROTECTED]> To: Drew Jenkins <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Saturday, March 3, 2007 5:00:26 AM Subject: Re: Can't Bind to Port >Try the command: >find / -name mysql 2>/dev/null >then you will find out if you have multip

Re: A simple 2 table query gets very slow when using ORDER BY

I agree that my testing data is wrong, but what I was wondering is why the query gets slow only when using join, although without join it gives me lightning answer. BTW, you gave me a big hint! I didn't know about 'cardinality', so searched on the web with the word 'cardinality', which leads me t

Re: A simple 2 table query gets very slow when using ORDER BY

It,s simple. Look at cardinality on last two rows of statement 'show index from e2s'. You have same cardinality on co_id and mod_time both. My solution stands on the fact that mod_time will have much higher selectivity in real data than co_id. (IMO) So, I suppose that you have wrong testing

Re: A simple 2 table query gets very slow when using ORDER BY

Thank you for answering. I tried your suggestion and it's got fast, but still slow and could not get rid of "Using temporary; Using filesort". I don't understand why this simple query is so slow... ---

Re: A simple 2 table query gets very slow when using ORDER BY

Hi, if your selectivity is very low, try to use multiple index on e2s(co_id, mod_time) and force this index in query. Filip employees2skills: INSERT INTO employees2skills SELECT id, 1, 2, NOW() FROM employees; # Yes, 100 have same skill_id, co_id, and even mod_time. # selectivity is ex

Re: Database table with unique no duplications

Why do you recommend using trigger instead of unique index? I think when unique index works I don't have any reason to use trigger. If the field you want to be unique is big one, like char(1000) or TEXT, You have to use trigger to save the uniqueness, though. On 3/2/07, Anoop kumar V <[EMAIL PRO

Re: A simple 2 table query gets very slow when using ORDER BY

Additional information. MySQL Version: 5.0.33 Platform: Mac OS X 10.4.8 Machine Spec: 2.16GHZ Intel Core 2 Duo Memory 2GB MHZ SDRAM Test was done using the following dummy data. employees table: 20 ascii chars as employees' name skills: INSERT INTO skills (name) VALUES ('C'), ('C++'), ('C#'),

A simple 2 table query gets very slow when using ORDER BY

Hi list, I have 4 simple table. --- CREATE TABLE employees( id INT NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, PRIMARY KEY(id), UNIQUE(name) ) ENGINE = InnoDB; CREATE TABLE skills( id INT NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL,

Re: Can't Bind to Port

Try the command: find / -name mysql 2>/dev/null then you will find out if you have multiple mysql installations on your system, which I consider highly likely. On 3 mar 2007, at 07.42, Drew Jenkins wrote: 26- Original Message From: Tommy Nordgren <[EMAIL PROTECTED]> To: Drew Jenkin