RE: Best choice for index
>> SELECT id_team, >> sum(IF(m.id_visitor = t.id_team,m.visitor_score, >> m.home_score)) AS But_pour, >> sum(IF(m.id_visitor != t.id_team,m.visitor_score, >> m.home_score)) AS But_contre >> FROM tab_teams t, tab_matchs m >> WHERE t.id_level =4 >> AND(m.id_visitor = t.id_team OR m.id_home = t.id_team) AND >> m.season = 2 >> GROUP BY id_team; >> Try rewriting this SQL query into a UNION all statement, by removing the OR condition in the WHERE clause, like: SELECT FROM WHERE m.id_visitor=t.id_team AND m.season=2 UNION ALL SELECT FROM WHERE m.id_home=t.id_team AND m.season=2 At least from what I know of Oracle this can speed up the query by orders of magnitudes and it maybe does the trick with mySQL, too. UNION ALL is supported with mySQL >= 4.x. Best regards ... Ralph ... - 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
DISTINCT - ORDER BY fields change result set
Hi, I encountered a problem with a DISTINCT query (mySQL-3.23.42). I need to order by a field not contained in the field set of the SELECT clause. The following query works correctly: SELECT DISTINCT p.lastchanged AS lastchanged, p.dam_id AS dam_id, d.dam AS dam, d.sire1 AS sire1, d.dam1 AS dam1, d.year AS year FROM products p LEFT JOIN dams d ON (p.dam_id=d.dam_id) WHERE p.lastchanged>='$s_from_date' AND p.active='Y' ORDER BY p.lastchanged DESC, d.dam ASC However I don't want to sort by "lastchanged", but by "changed" instead, which has a different meaning than lastchanged in my tables. But now every row shows up several hundred times, instead of being DISTINCTed. SELECT DISTINCT p.lastchanged AS lastchanged, p.dam_id AS dam_id, d.dam AS dam, d.sire1 AS sire1, d.dam1 AS dam1, d.year AS year FROM products p LEFT JOIN dams d ON (p.dam_id=d.dam_id) WHERE p.lastchanged>='$s_from_date' AND p.active='Y' ORDER BY p.changed DESC, d.dam ASC Any idea how I can get this sorted by changed, but correctly DISTINCTed? Regards ... Ralph ... - 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 question!
>Does anyone know how to write a query to get out the sum of a whole
>table column?
SELECT sum() FROM {WHERE };
Also see the manual.
:wq!
Ralph
-
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
mySQL 4
Hi, just read about the UNION operator beging supported with mySQL 4. How far are those operators implemented yet? Will there be any chance that UNION ALL and INTERSECT will be supported, too? And last but not least I want to try mySQL 4 but can't find any download link yet. Where can I download the latest version? Best regards ... Ralph ... - 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: Inno-DB, updating table problem
>> the table becomes corrupted. Is this some limitation of inno DB or should I >> separate the UPDATE process in two separate UPDATE statements? >It's a known and confirmed bug that will be fixed in 3.23.42. There >was an announcement just posted about this a few minutes ago. Hi Jeremy, thanks alot for your hint. I will check the list again, but seems like that mail hasn't reached me yet. Glad to hear it's not an issue only affecting my system. Thanks again for your great assistance. With my best regards ... Ralph ... - 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
Inno-DB, updating table problem
Hi, recently I converted a table containing a large amount of text fields from myISAM to inno-DB and encountered a problem I can't help with. The table structure consists of: textref_id INT(12) UNSIGNED textref_cat_id INT(12) UNSIGNED textgroup_key CHAR(5) NOT NULL texthier_keyCHAR(5) NOT NULL textidfc_oskCHAR(25) textcontent TEXT ... plus many other fiels, about 25 The primary consists of a concatenated key "textref_id, textref_cat_id". According to some internal structures, the primary key may change while the archiving process of newer texts goes on. Whenever I issue a statement like: UPDATE fulltext_arch SET textref_id=508281, textref_cat_id=881 WHERE textidfc_osk='MED-ADM-20010502-067'; the table becomes corrupted. Is this some limitation of inno DB or should I separate the UPDATE process in two separate UPDATE statements? The version I use is 3.32.41, linux, 2.2.19 kernel, gcc 2.95.2. If needed, I can provide a mysqlbugreport. With my best regards ... Ralph ... - 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: Can I do this ?
Hi Carlos, >SELECT * FROM some_table WHERE field1 = 1 AND field2 > 0 >ORDER BY CONCAT(field1,field2) LIMIT 1 You have to give the concatenated field an alias name and use it in the SELECT-subpart of your query, e.g. SELECT field1, field2, concat(field1,field2) AS sortvar1 WHERE field1=1 AND field2 > 0 ORDER BY sortvar1 LIMIT 1; Maybe this can be optimized further to improve the query speed, but that's another issue. Regards ... Ralph ... - 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: Reg. Synchronization between MySQL DBs
Hi, >Does anyone know an automated way of keeping two MySQL db's (located on >different machines) in sync? A small window of non-sync could be acceptable. Read about "replication" in the corresponding chapters in the online manual of mySQL (http://www.mysql.com/) -> Documentation. Regards ... Ralph ... - 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: Views !!
Hi, >How may create a view in mysql, o make some seem to the queiries in >access !!! As discussed already many times over and over again and as it can be read in the mySQL documentation (http://www.mysql.com) views are (not yet) implemented in the current 3.23.36 version. Regards ... Ralph ... - 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 3.23.34a - "Lost connection to MySQL server duringquery"
Hi Jordan, >Well... today I can't seem to reproduce the problem anymore. :/ I was >getting it very frequently yesterday (on almost every try). I'm not sure >what has changed since then. I'll let you know if the problem happens again >and try to narrow down the exact steps to reproduce. Is it possible that you had a replicated system running, where the master was your updated 3.23.34 and a slave still had 3.23.33 running? - The same happened to me last night after I updated one of my masters to 3.23.34, but still left the slave running 3.23.33 for later updating. After several operations running locally on the new master's version, the same error occured. It immediately disappeared after I upgraded the slave to 3.23.34 too. Never saw it again. Maybe only coincidence, but... who knows. Regards ... Ralph ... - 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
What index is needed to speed up query?
Hi folks, after having carefully tuned all statements within a larger web based application rewriting SQL statements and optimizing them by adding indices, there are still two statements left, which are awkward concerning performance. Maybe I am just too blind to see how to get them working faster, but I am puzzled at the moment. Can someone help me out with the following two SELECTs and their EXPLAINs, please? The only thing which changes from query to query is the date used in the WHERE clause AND the combination of the two active flags in the WHERE condition, which may be permutated giving all four possible combinations (p=Y/d=Y, p=N/d=N, p=N/d=Y and p=Y/d=N), alas this should not affect the querys execution plan in any way, if I see it right. With my best regards ... Ralph ... EXPLAIN SELECT DISTINCT p.dam_id, p.lastchanged FROM products p LEFT JOIN dams d ON (p.dam_id=d.dam_id) WHERE p.active='Y' AND d.active='Y' AND p.lastchanged>='2001-02-01' ORDER BY p.lastchanged ASC, d.dam ASC; +---+--+---+---+-+--+--- ---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+--+--- ---+-+ | p | ALL | NULL | NULL |NULL | NULL | 9137 | where used; Using temporary; Using filesort | | d | ref | dam_id_active_idx | dam_id_active_idx | 4 | p.dam_id | 1| where used; Distinct| +---+--+---+---+-+--+--- ---+-+ 2 rows in set (0.01 sec) EXPLAIN SELECT DISTINCT p.dam_id, p.lastchanged FROM products p LEFT JOIN dams d ON (p.dam_id=d.dam_id) LEFT JOIN gags g ON (d.dam_id=g.dam_id AND (ISNULL(g.year) AND ISNULL(g.product_id)) OR (NOT ISNULL(g.year) AND g.product_id=g.dam_id)) WHERE p.active='Y' AND d.active='Y' AND p.lastchanged>='2001-02-01' ORDER BY p.lastchanged ASC, d.dam ASC; +---+--++---+-+- -+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--++---+-+- -+--+-+ | p | ALL | NULL | NULL |NULL | NULL | 9137 | where used; Using temporary; Using filesort | | d | ref | dam_id_active_idx | dam_id_active_idx | 4 | p.dam_id |1 | where used; Distinct| | g | ALL | dam_id_year_active_idx | NULL |NULL | NULL | 287 | Distinct| +---+--++---+-+- -+--+-+ 3 rows in set (0.00 sec) - 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
