GROUP BY vs DISTINCT - questions
Hi, 1. What is the difference between GROUP BY and DISTINCT in the background engine? How MySQL treats each one of them? 2. Why GROUP BY statement ALWAYS uses a temporary file??? 3. Why using LIMIT with a GROUP BY statement takes about the same time as without using LIMIT? any answer would be appriciated... -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 - Full-Text using UTF-8
Hi, How do I search and index a TEXT column to use the UTF-8 charset? Do I need to define the FULLTEXT index or the column definition in a special way? I tried to use it as usual as I use full-text search on English only, but it seems not to match... (it's not the ft_min_word_len or the 50% treshold).. -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 - Full-Text using UTF-8
Mabye it is something connected to my.ini definitions? mysql SHOW VARIABLES LIKE 'character%'; +--+ -+ | Variable_name| Value | +--+ -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\Apache Group\mysql\share\charsets/ | +--+ -+ 7 rows in set (0.01 sec) -thanks, Lorderon. Lorderon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, How do I search and index a TEXT column to use the UTF-8 charset? Do I need to define the FULLTEXT index or the column definition in a special way? I tried to use it as usual as I use full-text search on English only, but it seems not to match... (it's not the ft_min_word_len or the 50% treshold).. -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select query that uses a temporary table
Hi All, There's something that bothers me.. I have a query that uses a temporary table (has a necessary GROUP BY clause). The query also uses ORDER BY clause (necessary too). And I also use LIMIT clause. If the query finds 10,000 rows, then MySQL will insert 10,000 rows into the temporary table and sort, which makes the query very slow... :( Any suggestions on how can I speed it up? (I would be satisfied with the top 500 rows, no need in all the 10,000) Mabye, is there a way to tell MySQL to limit the temporary table up to 500 rows? so, when a row is matching into the top 500 rows, the last row will be dropped out (in case the table is on limit), and the new matched row will be inserted into the right place in the temporary table... -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM transactions
Will MyISAM support transactions in the future versions? Is it possible? -thaks, Lorderon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text
Hi..
When doing the next query, the rows are not ordered automatically by the
coefficient of the full-text.. Why???
SELECT id,update_time FROM table1 INNER JOIN ft_table USING (id) WHERE MATCH
(title,content) AGAINST ('class') LIMIT 0,50;
thanks,
-Lorderon.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text with JOIN
Hi, Sergei Golubchik [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi! On Mar 20, Lorderon wrote: I have 3 tables to join when the last one is a Full-Text table (ft_table).. I do the next join: SELECT id,title FROM table1 LEFT JOIN table2 USING (id) INNER JOIN ft_table USING (id) WHERE ... But MySQL selects the primary key (id) to join the ft_table, which makes the query run a lot of time and gives wrong results according to the MATCH AGAINST search.. Please provide a complete repeatable test case for this. That was wierd.. now I get reasonable search times.. I found that making the join as this: SELECT id,title FROM table1,table2,ft_table WHERE table1.id=table2.id AND table2.id=ft_table.id AND ... gives the wanted results according to MATCH AGAINST, but leave out rows that don't exist in table2 (the join there was LEFT JOIN).. 1- Is there a way to join the full-text table and using the full-text index, so the query will not last long? You may use USE INDEX / IGNORE INDEX in the FROM clause (see the manual) 2- Is there a way to make something like LEFT JOIN using list of tables seperated by comma (table1,table2,..)? no. 3- Is there a performance difference between making INNER JOIN or by making list of tables seperated by comma (table1,table2,..) with using WHERE clause? no. Then I'll build the query using list of tables seperated by comma (table1,table2,..) I also found that when you use JOIN with full-text, MySQL don't automatically sort the results by the coefficient of the full-text... when you use list of tables seperated by comma MySQL sorts it correctly... Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com Thanks for the help :-) -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text with JOIN
I have 3 tables to join when the last one is a Full-Text table (ft_table).. I do the next join: SELECT id,title FROM table1 LEFT JOIN table2 USING (id) INNER JOIN ft_table USING (id) WHERE ... But MySQL selects the primary key (id) to join the ft_table, which makes the query run a lot of time and gives wrong results according to the MATCH AGAINST search.. I found that making the join as this: SELECT id,title FROM table1,table2,ft_table WHERE table1.id=table2.id AND table2.id=ft_table.id AND ... gives the wanted results according to MATCH AGAINST, but leave out rows that don't exist in table2 (the join there was LEFT JOIN).. 1- Is there a way to join the full-text table and using the full-text index, so the query will not last long? 2- Is there a way to make something like LEFT JOIN using list of tables seperated by comma (table1,table2,..)? 3- Is there a performance difference between making INNER JOIN or by making list of tables seperated by comma (table1,table2,..) with using WHERE clause? thanks a lot in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
limit stop count
How can I limit a count to stop when he reaches 200 rows? SELECT COUNT(*) FROM tbl WHERE where_clause;= returns 3500 I want to stop the counting when it reaches to 200, so MySQL will not search further to count all the 3500 rows.. thanks, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: limit stop count
Nope.. that doesn't work.. the LIMIT statement limits the rows returned, but only 1 row is returned from that query always.. I want MySQL to stop the count when it reaches to 200, rather to have it go over all the table and return me 3500.. SELECT COUNT(*) FROM tbl WHERE where_clause limit 200; -Original Message- From: Lorderon [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 17, 2004 6:45 PM To: [EMAIL PROTECTED] Subject: limit stop count How can I limit a count to stop when he reaches 200 rows? SELECT COUNT(*) FROM tbl WHERE where_clause;= returns 3500 I want to stop the counting when it reaches to 200, so MySQL will not search further to count all the 3500 rows.. thanks, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB 1024
Hi All, How can I define a column of BLOB (or TEXT) with exact length of 1024 bytes? Can I index it? I want to run a select query using FIND_IN_SET function on that column.. but I'm afraid it will be slow.. any suggestions? thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text on double rows
If I got a full-text table with X rows, and some search takes 5 sec.. Now if I got a full-text table with 2X rows, how much time will take the same search on twice amount of rows? Is the difference linear??? thanks in advance, -Lorderon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text on double rows
Chris Nolan [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Which version of MySQL?? The difference will be different (!!) depending on the version in use. In general, full text search uses a tree-structure. Doubling the number of entries in the tree is likely to result in a time difference of not very much at all! Currently using version 4.0.16 but will move to 4.1 and 5 when they're released.. How it works on those versions? According to what you described as a tree-structure.. then spliting a big full-text table into 2 smaller tables and use UNION just makes the situation worse... thanks -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP and using mysql_last_id()
It is better run a query with: SELECT LAST_INSERT_ID(); immediately after making the insert query. that way you'll get the exact ID.. Elly Wisata [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Does php have a function like mysql_insert_id but support a zerofill auto increment int? ~Elle~ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 17, 2004 5:19 AM To: [EMAIL PROTECTED] Subject: Re: PHP and using mysql_last_id() Hi can anyone tell me how to use php's mysql_last_id() or MySQL's INSERT_LAST_ID - php's function is mysql_insert_id() and not mysql_last_id(), there is no php function called mysql_last_id()...if you saw that in a manual, i'd email the owner of the document. http://php.net/mysql_insert_id hth Jeff _ I have a page registration page, using a username, password and member level text box. I want to send the user (upon successful insert) to a member's detail form where the password table primary key is avaliable it can be kept and entered to subsequent tables as a Foriegn Key. But when I have tried it all I ever get is a value of 1. I have looked at the manuals online and found a couple of books that list these functions, and understand the syntax qnd what it does, but can not find a working example or inducation as to where it goes etc any pointers would be much appreciated stu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
found rows in union
How can I find the number of rows a query returns when I'm using UNION ? for example, how can I know how much rows the next query returns: (SELECT price FROM table1 WHERE id100) UNION (SELECT price FROM table2 WHERE id150) thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: found rows in union
I'm running MySQL 4.0 and it doesn't support sub-queries... I've checked and found that the next query returns rows of counting each union part seperatedly and could make sum on its rows: (SELECT COUNT(*) FROM table WHERE id100) UNION ALL (SELECT COUNT(*) FROM table2 WHERE id150) returns: ++ | COUNT(num) | ++ |124 | |912 | ++ When running the query without union, you could use SQL_CALC_FOUND_ROWS even when having LIMIT, but this option not works when using union.. :( Is there anything like SQL_CALC_FOUND_ROWS in union? since I don't want to run the query twice Also... if I run the same query twice.. first run and selecting columns.. then I run the same query but selecting COUNT(*).. does the second time will run using MySQL's cache? Lorderon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] How can I find the number of rows a query returns when I'm using UNION ? for example, how can I know how much rows the next query returns: (SELECT price FROM table1 WHERE id100) UNION (SELECT price FROM table2 WHERE id150) thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sub-query
Since I don't use MySQL 4.1, is this query OK? SELECT SQL_CALC_FOUND_ROWS * FROM ((SELECT * FROM t1 WHERE id100 GROUP BY country) UNION ALL (SELECT * FROM t2 WHERE id150 GROUP BY country) LIMIT 0,10); I want this to return all the rows of the UNION sub-query there.. the reason I used it as sub-query is that I want to count the rows, and then use: SELECT FOUND_ROWS(); to get the number of rows supposed to be retuned without using the LIMIT.. Is all above correct? thanks, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNION
Hello All, I want to sum a column from several tables while using UNION.. something like this: (SELECT SUM(price) FROM table1) UNION (SELECT SUM(price) FROM table2) This results in 2 rows that sums each table seperatedly.. but I want to sum the column from both tables together.. I usually have more than 2 tables to sum, and I sum different tables every time, so I can't define merge tables on every combination.. Is there a way to sum the column from all tables together? thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION
the only why i know how to do this is to write the ind sums to a new table and then sum that table.. How you do it with a new table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select speed
Hi All, If I got one table A_table with many columns, and a second table B_table is the same but with just primary field and unique field... How much meaningful is the time difference between these queries? 1. SELECT unique_field FROM A_table WHERE prim_field='val'; 2. SELECT unique_field FROM B_table WHERE prim_field='val'; If I split A_table into some tables, and define C_table to be MERGE on the A_table pieces. Is the time difference between selecting from A_table or C_table is meaningful? thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text Search on MERGE Tables
Hello All, Is it possible to define MERGE table on several tables with full-text indexes? And to make a select on the MERGE table with MATCH AGAINST? thanks, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Joins
You might want to append table to table.. in this case you should use UNION (not JOIN).. but if you got 2 identical tables of type MyISAM, then you can define a MERGE table like this: CREATE TABLE new_table (*table definition of the original tables*) type=MERGE union=(all_by_Payroll,payinc); then you can run the select query on the new_table. Unknown Sender [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I have 2 identical tables and wish to join them. I am a complete novice and thought it was simple! Here is the code that I am using with asp.net select Date, Payroll, First, Last, Rank, Number, Division, Reason, ImpDate from all_by_Payroll, payinc where + DropDownList1.SelectedItem.Value + = ' + TextBox1.Text +' ORDER BY Date ASC; Any help would be appreciated as I am now completely stuck Thanks, Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump
Hello All, How can I dump selected rows into a file (using a query or mysqldump)? i.e, I want to dump only the rows of this query: SELECT * FROM tbl WHERE id100 AND id200; thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Union EXACT Tables
Hello All, Is there a way to make concat 2 tables together without using UNION ? The tables are EXACTLY SAME, but they sit on 2 different DBs. And I want to run the SAME where clause on both tables... Thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Union EXACT Tables
Paul DuBois [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 19:04 +0200 2/24/04, Lorderon wrote: Is there a way to make concat 2 tables together without using UNION ? The tables are EXACTLY SAME, but they sit on 2 different DBs. And I want to run the SAME where clause on both tables... What's the problem with using UNION? It's quite slow, when I have large tables.. Here I got the same tables and I want to run the same where clause, so mabye there's something faster that I can use? Thanks, -Lorderon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Union EXACT Tables
Thanks for your help... appreciate it... MERGE table type is what I searched for :-) Thanks -Lorderon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search
Santino [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Is it possible to create a InnoDB table and a MyIsam table with fulltext indexes and use a join to search in fulltext indexes? Santino Yup.. you can.. create the tables on the same DB and make a JOIN.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto sorting
Hello All, I got a table with a VARCHAR(255) field as a primary key, but when I insert a new row it inserts it ordered by the key. Then, when I select rows without doing any order, it returns the rows ordered by the primary key. How can I insert a new row to the end of the table, and select records will not use auto sort, but without doing an ORDER BY clause or making an auto_increment primary key? thanks in advance, -Lorderon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
