| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14
Comments:
<0> thanks TodoInTX
<1> ilia: it is a restriction now, may be removed in the future though
<2> wiki-ize: select LEFT(this, 5) as this, LEFT(that, 5) as that from articles;
<3> todo: WOWWW!!!
<2> lol!
<3> Damn, that work slike a charm.
<2> easily impressed ;)
<4> lokus, are there any restriction on insert statements performed inside BEFORE DELETE trigger?
<3> todo: Damn, Sam, that is really going to make my life a lot easier. Many thanks!
<5> Hey, all - may I request review/critique of a painfully twisted query?
<6> put it in a pastebin
<5> I did: http://hashmysql.org/paste/viewentry.php?id=1507
<1> bigClown: can it be simplified?
<5> Well, that's sort of my question.
<5> Can the tables be simplified? no. they're as basic as they get. Can the query be simplified? Durned if I know - but I didn't see how.
<1> bigClown: try to execute the inner query alone and see the result
<1> bigClown: so you need left join in the inner query?
<5> Oh, it works, both the inner query and the outer. With the inner query, I get solely the records containing ANY contact record ***ociated with the current utility.
<1> bigClown: do you need left join in the inner query?
<5> Don't think so. I just wanted to see if there was an easier way.
<1> bigClown: you might get more than what you need with left join
<1> bigClown: sorry, you won't
<1> bigClown: inner join is sufficient, no need left join in the inner query
<5> That's what I figured - it's working, so I'll just look at it and go, "DOH!" now and then.
<5> Cool. Mostly, I was worried about the sub-query, as I'd never used 'em in MySQL.
<6> well if it ain't broke...
<5> yep. Bob, I like your thinking.
<5> It ain't but after thinking that particular query out, I sure am.
<1> bigClown: maybe it is possible to put the inner query into join to the outer query
<5> Would that gain me anything, lokus?
<1> bigClown: maybe yes maybe not, explain may tell
<4> are there any known issues with using triggers that contain INSERT with ON DUPLICATE KEY statements?
<6> bigClown, What does EXPLAIN say about it?
<5> dunno - I'll try it.
<1> ilia: what did you find out?
<1> ilia: before insert trigger? is it executed when there is duplicates?
<4> it is an AFTER INSERT trigger
<1> ilia: is it executed when duplicates?
<4> no no, let me explain better
<4> there is table A
<4> when insert into table A happens, a trigger performs an insert into table B
<1> ilia: i expect it no to execute , not sure about before insert
<1> trigger in table B is not executed
<4> the trigger insert contains on duplicate key statement that in the event of a dupe updates a row counter in table B
<5> OK, I updated the paste to include the EXPLAIN: http://hashmysql.org/paste/viewentry.php?id=1507
<4> what I am seeing is that when I insert into A, counter in B is much greater then expected
<5> sorry, ilia - didn't mean to intrude. Or extrude, or whatever.
<1> bigClown: you have dependent subquery, not a good sign
<1> ilia: you mean it got updated multiple times?
<4> lokus, yeah
<5> I thought my inner query WAS a dependent subquery.
<5> I don't think I understand dependent subquery - gonna take a look on mysql.com
<6> is that a representative amount of data or only a small test set?
<5> small test set - approx 5% of complete dataset.
<1> ilia: i don;t have much experience with trigger, but it should not be, the statement in insert trigger is executed once for every row inserted in table A
<6> !man dependent subquery
<7> Nothing found.
<6> rats
<1> dependent query means something like need to be executed once for every row of the outer query
<4> lokus, logically I agree it shouldn't be, but it is :P unless I am missing some critical bit somewhere
<1> ilia: can create a simple reproducible test case and report a bug
<4> yeah, working on that now
<6> !man optimizing subqueries
<7> (Optimizing Subqueries) : http://dev.mysql.com/doc/mysql/en/Optimizing_subqueries.html
<5> Ah. So, as I add more Service records, it'll increase the number of times the inner query is returned. Ideally, I need to split that out so that it only does that inner query once.
<1> bigClown: i believe you can remove the subquery altogether, replace with join
<1> bigClown: check the for rewriting subquery with joins
<5> Hmm. I'm looking at that now. lemme see what I can find. Thanks, lokus and bobfield!
<6> !man rewriting subqueries as joins
<7> (Rewriting Subqueries as Joins for Earlier MySQL Versions) : http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html
<0> anyone know much about fine-tuning the MySQL cache on my system?
<1> jbrimble: what cache?
<0> the details in my.cnf.. Query_cache, key_buffer.. that sort of thing
<0> I need more performance
<1> jbrimble: what is the bottleneck? every configuration depends to many factors
<0> what do you mean by bottleneck? sorry, i'm new to this
<1> jbrimble: if you have a critical system to optimise http://www.mysql.com/consulting/packaged/performance.html
<1> jbrimble: then forget it, identify the problem first then fix it, if you don;t know the problem, what do you expect
<0> understood
<1> jbrimble: if it is so simple, there will be no need of such config file
<6> is it a particular query running slow?
<8> helo
<8> someboyd here?
<4> lokus, here is the reproduce SQL http://70.87.49.124/debug.sql
<9> when i go to my webserver at http://localhost/ it wont automatically show my index.php file up what do i have to do to get it to show up
<10> Smok3y, Go ask in #apache
<11> you need some Add-Handler: stuff added to your webserver configuration. I'm sure it's listed in some "installing PHP" guide somewhere, but that's not really relevant to #mysql.
<1> ilia: check the select and the order of the fields in calendar_summary, seems that it is wrong order for the last 2 fields
<1> ilia: run the select with newid=76 and select * from calendar_summary, you should see the problem there
<12> I'm making a limited search engine and I want to know if is it a good idea to set the fields that will be seached with FULLTEXT index.
<10> Yes
<4> lokus, select in the trigger?
<1> yes
<1> insert and selet fields order
<4> I must be blind but I don't see the inconsistency
<1> ilia: wait, nevermind, my mistake,
<13> How do I allow a user to grant permissions using mysql's ricky retardo user management? The user already has Super_priv = Yes.
<1> ilia: try to run the last insert 1 row value at a time and select * from calendar_summary each time and you may see what is happening
<4> lokus, same situation
<4> for 1st record its ok
<4> on 2nd insert, the newly inserted record is fine, but the previous one is now twice as large
<12> Sorry for repeating, but I really need this answer: I'm making a limited search engine and I want to know if is it a good idea to set the fields that will be seached with FULLTEXT index.
<1> ilia: because the select in the trigger selected it and a duplicate key occur, so the av is increased
<1> ilia: 1 row inserted and 1 row updated
<14> Anjo scroll up will answered you straight away
<4> lokus, how so, the join is always 1 to 1, no?
<1> ilia: the 2nd insert , the select in the trigger should return 2 rows now, 1 row inserted as new row and the other cause a duplicate key, so the update is executed
<4> ah yes... you're right
<4> thanks
<12> archivist: Sorry, I missed it.
<12> will: Did you answer me or lokus?
<5> Hrm. still working on that paste, but I still don't see it. I can get it to return all recordSets referencing the current Utility record, and I can get it to return a recordSet referencing Service records referenced by NO Utility, but I can't figure how to optimize this ****er to return the recordSet containing Service records NOT referenced by this utility.
<1> bigClown: you need left join for not in, i thought there are example in the manual
<5> so I can still use the NOT IN? Because this ****er ain't working with IS NULL.
<12> archivist: I don't know if the "yes" was for me.
<12> archivist: Do you know about the FULLTEXT index?
<4> lokus, thanks again, it was a really silly error on my part there
<12> I'm making a limited search engine and I want to know if is it a good idea to set the fields that will be seached with FULLTEXT index.
<1> ]Anjo[: you got your answer, if you add fulltext, you need to use the fullext to gain the benefit
<1> !m ]Anjo[ fulltext
<7> ]Anjo[: (Full-Text Search Functions) : http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
<12> Thanks a lot.
<12> I am sorry for bugging you guys.
<12> I didn't get the answer before.
<15> is there a way to save the result of a 'select' as a table?
<15> and then do queries on this new "table" ?
<1> !m tktktk create table select
<7> tktktk: Nothing found
<1> !m tktktk create table
<7> tktktk: (CREATE TABLE Syntax) : http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
<1> tktktk: check the one with select syntax
<15> lokus, thanks :-)
<15> what's the commetn char in mysql?
<15> oh, #, n/m
<16> can someone tell me whats wrong with my trigger? I checked all queries, 10 times now, and I still can not find error.
<16> DELIMITER |
<16> CREATE TRIGGER all_mail BEFORE INSERT ON conversation_labels
<16> FOR EACH ROW BEGIN
<16> SET @allMailLabelID :=(SELECT l2.ID FROM labels l2 WHERE l2.NAME LIKE 'All Mail' AND l2.Account_ID = (SELECT l.account_ID FROM conversation_labels cl,labels l WHERE cl.Labels_ID = NEW.Labels_ID AND l.ID = cl.Labels_ID GROUP BY l.Account_ID) limit 1);
<16> SET @convNum :=(SELECT COUNT(*) FROM conversation_labels WHERE conversation_ID = NEW.conversation_ID);
<16> IF @allMailLabelID>0 THEN
<16> IF @convNum=0 THEN
<16> INSERT INTO conversation_labels (conversation_ID, labels_ID) VALUES(NEW.conversation_ID,@allMailLabelID);
<16> ELSE IF @convNum=1 THEN
<16> SET @labelID := (SELECT labels_ID FROM conversation_labels WHERE conversation_ID = NEW.conversation_ID);
<16> ?
<17> Sashanson: first of all, NEVER FLOODPASTE
Return to
#mysql
or
Go to some related
logs:
tool imap2pop3
FATAL ERROR: Cannot open disk drive
Press any key t
suse 10.0 pvm
#perl
#python
mysql composite index date time
ubuntu monkeyaudio plugin
!!! Unable to build DRM modules.
#web
#gaim
|
|