RE: Is it possible to get a timestamp from the server ?
Yea do a search on the mysql.com web site for date functions (or just click here http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html ) its loaded with many examples and descriptions of useful functions. Built in date calculations are one thing I really enjoy with mysql. Best, Yoed -Original Message- From: Greg Hosler [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 7:08 PM To: [EMAIL PROTECTED] Subject: Is it possible to get a timestamp from the server ? Hi, I have a database application that I am porting to MySQL. one of the things that the application needs to do is to get a timestamp from the server, so that all instances of the application across a network can timestamp cewrtain records using a common source for the timestamp. e.g. the sql server. In the past, under Informix, we did the following sql: SELECT distinct current year to second FROM systables I've looked around, and I'm not seeing anything equivalent under MySQL. Have I hopefully missed something ? thx for any suggestions / hints / pointers. best rgds, -Greg Hosler +-+ You can release software that's good, software that's inexpensive, or software that's available on time. You can usually release software that has 2 of these 3 attributes -- but not all 3. | Greg Hosler [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]
SLOW 22million rows, 5 hour query?
I have a table with 22,371,273 rows, current type is MyISAM. I have one
query tha took nearly 5 hours to complete. I do not know if this like
it should be or not, but I thought I'd ask the list. My gut feeling is
the `gropu by` that's slowing it down but nonetheless 5 hours seems
excessive. I'm trying to find the source of the limitation and work
from there.
Mysql:
4.0.18-standard, precompiled 32-bit sparc 2.8
Server:
Sun 420
Solaris 2.8
4x450MHZ Ultrasparc-II
4GB Ram
Two 50gb mounts, fiber channel scsi to EMC.
Brand new systems, totally idle.
I think everything relevant is here. The skinny: full table scan on
22 million rows, group and insert into new table. MyISAM and InnoDB
appear to give similar results when used as the destination
('new_table').
insert into new_table
select month_day, floor(bucket/3) as bucket, date, src, avg(value) as
value
from source_table
group by month_day, bucket, src;
Relevant `explain` details:
Full table scan: 22,371,273 rows, Using temporary; Using filesort
Query OK, 11495208 rows affected (4 hours 47 min 21.01 sec)
Records: 11495208 Duplicates: 0 Warnings: 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql as a spatial database backend
Ive posted thsi query twice and got no reply - Im sure somebody must know thw answer!!! whast the field length limitations for insertion of a WKT string into a geometry column? Maybe it's because 1) not many people use spatial data in MySQL 2) the question is rather theoretical yours, close to giving up Don't. Maybe post an example of the database structure (CREATE statement) and an example of what you try to execute, any errormessages, etc.? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
insert into new_table select month_day, floor(bucket/3) as bucket, date, src, avg(value) as value from source_table group by month_day, bucket, src; Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort Query OK, 11495208 rows affected (4 hours 47 min 21.01 sec) IMHO two things slow you down: 1) floor(bucket/3) as bucket. This means that MySQL has to calculate this value for each record to be able to use it as a grouping value. If you always use this same expression for grouping, it might be useful to have a column with the precalculated value. 2) there's only one table involved, so only one index will be used. If you had an index that contains month_day, bucket and src it would speed up things perhaps. Furthermore, it depends on the speed of the disks, your configuration, memory configuration and use how fast it will go. But explain indicates that MySQL needs to copy the data into a temporary table and use filesort to order and group things. Maybe it will help to surpress the automatic sorting that is done by MySQL because of the GROUP BY, by adding ORDER BY NULL? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first LIMIT then ORDER
Interesting comment. I find by experiment that (select * from FOO order by a desc limit 10) order by a; removes duplicates, but, if I drop the second order clause, (select * from FOO order by a desc limit 10); duplicates are retained. Why is the first a union, but not the second? Just curious. From: Keith C. Ivey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Fri, 23 Apr 2004 11:27:38 -0400 Subject: Re: first LIMIT then ORDER On 23 Apr 2004 at 7:23, Bill Easton wrote: The last suggestion is useful when you do care which entries you get, as you can use one order for limit and another for presentation. For example, if you'd like the LAST 10 rows, but sorted in FORWARD order, you can use something like (select * from HISTORY order by version desc limit 10) order by version; And I thought I'd have to wait for subqueries... One small gotcha that Anders Karlsson pointed out to me through Paul DuBois: This one-query union syntax doesn't allow you to use the ALL keyword after UNION (since the UNION keyword isn't even there). That means it will always eliminate duplicate rows (like DISTINCT). That hasn't come up when I've used it, since I've never been selecting result sets that could contain duplicate rows, but it's something to keep in mind. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first LIMIT then ORDER
I find by experiment that (select * from FOO order by a desc limit 10) order by a; removes duplicates, but, if I drop the second order clause, (select * from FOO order by a desc limit 10); duplicates are retained. Why is the first a union, but not the second? Just curious. On http://dev.mysql.com/doc/mysql/en/UNION.html you can see a comment by Keith Ivey about this. Apparantly it's caused by the fact that (SELECT .) UNION (SELECT) [ORDER BY ] is the syntax for a UNION. If you leave the first table out, you're left with: (SELECT ) ORDER BY... The fact that there are parentheses and an ORDER BY outside these parentheses seems to make it a UNION. If you leave out the ORDER BY..., it's just a query with parentheses around it. The manual states that if you do not use the keyword ALL with the UNION, it's considered to be DISTINCT. So, leaving out the UNION keyword entirely automatically makes it using DISTINCT. Regards, Jigal. From: Keith C. Ivey [EMAIL PROTECTED] DuBois: This one-query union syntax doesn't allow you to use the ALL keyword after UNION (since the UNION keyword isn't even there). That means it will always eliminate duplicate rows (like DISTINCT). That hasn't come up when I've used it, since I've never been selecting result sets that could contain duplicate rows, but it's something to keep in mind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Compound Primary Key question
As Jeremy says - it depends totally on what you want to do. If you have tables where there is no logical, unique way to identify that column (or the only way to do so is via a column you do not want to use for this purpose), then assigning a separate ID column as a PK makes sense. E.g: If you have a lookup table 'ItemDescription' which contains a list of description fields for items, it would make sense to make the table (ItemID, Description) with ItemID being an autoincrement primary key. However, in some other cases, a compound key will make more sense - for instance if you have a 'glue table' such as 'Item_Shop' which lists the items that are available in each shop: (ItemID, ShopID), then clearly, you cannot have a PK on either column alone (since there is a many to many relationship), so a compound PK is the only way to actually put a PK on the table (and uniquely identify a given row). One rule of thumb is: If there are two or more columns within a given table which together are the logical way to identify that row (and the way you would always join to the table), then use those as a compound key, otherwise assign a separate autoincrement column as a PK. Cheers, Matt -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 23 April 2004 23:51 To: Emmett Bishop Cc: [EMAIL PROTECTED] Subject: Re: Compound Primary Key question On Fri, Apr 23, 2004 at 03:40:43PM -0700, Emmett Bishop wrote: Quick question. In general, is it better to create compound primary keys or use an auto increment field to uniquely identify each record? Yes. It depends on your application and your data. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- 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]
Re: SLOW 22million rows, 5 hour query?
On 23 Apr 2004, at 9:48 pm, [EMAIL PROTECTED] wrote: Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort The filesort is a giveaway. Can you increase the sort buffer size so that the sort can happen in memory rather than having to use a file to sort? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first LIMIT then ORDER
As I stated before, my guess that duplicates are removed is because the SELECT is handled like one part of a UNION (I'll have a look at the code later to check if this is the case). Really, a UNION should consist of two or more SELECTs, so this is not the expected behaviour. The way this REALLY should be interpreted would be as a subquery followed by an ORDER BY. But as 4.0 doesn't have subqueries, this is not an option. But in 4.1 it is. I just tested it in 4.1, and rightly so, duplicates are NOT removed from this: (SELECT ...) ORDER BY ...; There is another way to write this query, which is like this: SELECT av.c1 FROM (SELECT c1 FROM t1) av ORDER BY av.c1; In this case av is an alias for the subquery (this is sometimes called an anonymous view, which is why I give it the alias av). An then, if we add a LIMIT clause to this, we get: SELECT av.c1 FROM (SELECT c1 FROM t1 LIMIT 3) av ORDER BY av.c1; The first construct, without the leading SELECT, is also, as far as I can interpret SQL-92/99, a standard SQL construct. But I think there might be a debate on this. The latter two construct ARE clearly SQL-92 compatible though (with the obvious exception of the LIMIT clause of course). And by the way, in a UNION, there is no need to put parenteses around the unioned queries in the general case. So (SELECT .) UNION (SELECT) [ORDER BY ] Is the same as SELECT . UNION SELECT [ORDER BY ] I say in the general case, as there are cases when the parenteses are required, in particular when the individual SELECT is followed by a MySQL specific construct or keyword. If I remember things correctly for example, this (SELECT ORDER BY...) UNION (SELECT... ORDER BY...) [ORDER BY ] will require the parenteseses, but this construct is a MySQL extension to the standard (an ORDER BY is not part of a query specification which is this form of a subquery). And yes, I know that the above query is a bit meaningless :-) Anyway, to summarize my view on this. An alternative way to achieve the requested operation is (which is fully SQL-92/99 except for the LIMIT clause): SELECT av.c1 FROM (SELECT c1 FROM t1 LIMIT 3) av ORDER BY av.c1; But this is available in 4.1 only. In 4.0 you can write: (SELECT c1 FROM t1 LIMIT 3) ORDER BY c1; Although this later syntax does not seem to work properly in 4.1.1 right now. (the LIMIT clause in this case has no effect, I get all rows back. Also note that the syntax doesn't allow for an alias for the anonymous view in this case). And neither of these constructs has anything to to with a UNION or a UNION ALL, really, except the latter is interpreted as being part of something like that in 4.0 (or so it seems). And now I close the SQL-92 standard docs. It is saturday after all and a beautiful day outside! Anders Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Jigal van Hemert wrote: I find by experiment that (select * from FOO order by a desc limit 10) order by a; removes duplicates, but, if I drop the second order clause, (select * from FOO order by a desc limit 10); duplicates are retained. Why is the first a union, but not the second? Just curious. On http://dev.mysql.com/doc/mysql/en/UNION.html you can see a comment by Keith Ivey about this. Apparantly it's caused by the fact that (SELECT .) UNION (SELECT) [ORDER BY ] is the syntax for a UNION. If you leave the first table out, you're left with: (SELECT ) ORDER BY... The fact that there are parentheses and an ORDER BY outside these parentheses seems to make it a UNION. If you leave out the ORDER BY..., it's just a query with parentheses around it. The manual states that if you do not use the keyword ALL with the UNION, it's considered to be DISTINCT. So, leaving out the UNION keyword entirely automatically makes it using DISTINCT. Regards, Jigal. From: Keith C. Ivey [EMAIL PROTECTED] DuBois: This one-query union syntax doesn't allow you to use the ALL keyword after UNION (since the UNION keyword isn't even there). That means it will always eliminate duplicate rows (like DISTINCT). That hasn't come up when I've used it, since I've never been selecting result sets that could contain duplicate rows, but it's something to keep in mind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SLOW 22million rows, 5 hour query?
Use insert delayed, and you will cut your time in half. At least with my experience. But also how long does it actually take to run the query itself. Giving a summary explain doesn't help much. You really need a table structure that the select is using and a full explain. Donny -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 6:02 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: SLOW 22million rows, 5 hour query? On 23 Apr 2004, at 9:48 pm, [EMAIL PROTECTED] wrote: Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort The filesort is a giveaway. Can you increase the sort buffer size so that the sort can happen in memory rather than having to use a file to sort? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- 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 Audit Checklist/Program
Hi, Is there any recommended MySQL Auditing guideline available somewhere on the Net? (Anything other then the recommededation mention in the official documentation). If there's any IT Auditor out there who would like to share his/her work? Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index Question
Hello,
I'm trying to create some summary data using some existing InnoDB tables
and I'm running into performance issues. Here is the query:
select topicx, count(*) from BillVote t1 left join BillVotestudentRelation
t3 on t1.mvcoid=t3.idstudent_p left join Student t2 on
t3.idstudent_c=t2.mvcoid left join BillVotebillRelation t5 on
t1.mvcoid=t5.idbill_p left join Bill t4 on t5.idbill_c=t4.mvcoid where
(((t2.usernamex)=(?) and (t4.committeeStatusx)=('P'))) group by topicx
The MySQL EXPLAIN command provided the following information:
+---++--+-+-++---+--+
| table | type | possible_keys| key
| key_len | ref| rows | Extra
|
+---++--+-+-++---+--+
| t1| index | NULL |
PRIMARY | 32 | NULL | 33297 | Using index; Using temporary;
Using filesort |
| t3| ref| PRIMARY |
PRIMARY | 32 | t1.mvcoid | 1 | Using index
|
| t2| eq_ref | PRIMARY,usernamex|
PRIMARY | 32 | t3.idstudent_c | 1 | Using where
|
| t5| ref| PRIMARY |
PRIMARY | 32 | t1.mvcoid | 1 | Using index
|
| t4| eq_ref | PRIMARY,committeeStatusx,committeeStatusx_topicx |
PRIMARY | 32 | t5.idbill_c| 1 | Using where
|
+---++--+-+-++---+--+
Is there an index I can add to improve the performance of this query? Any
help would be greatly appreciated.
Thanks.
Jeff Gunther
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first LIMIT then ORDER
From: Anders Karlsson [EMAIL PROTECTED] And by the way, in a UNION, there is no need to put parenteses around the unioned queries in the general case. So (SELECT .) UNION (SELECT) [ORDER BY ] Is the same as SELECT . UNION SELECT [ORDER BY ] I say in the general case, as there are cases when the parenteses are required, in particular when the individual SELECT is followed by a MySQL specific construct or keyword. If I remember things correctly for example, this (SELECT ORDER BY...) UNION (SELECT... ORDER BY...) [ORDER BY ] will require the parenteseses, but this construct is a MySQL extension to the standard (an ORDER BY SELECT . UNION SELECT [ORDER BY ] is actually ambiguous IMHO. Is the last (optional) ORDER BY part of the SELECT or the UNION? Maybe that's why a (SELECT...) ORDER BY... is interpreted as a UNION (without the UNION keyword). Anyway, this behaviour is not documented, so I wouldn't rely on it. Also, I can't see the point of using it in any real life situation, so let's move on with more urgent matters: weekend ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unixware 7.1.0 compile error... mysql 4.0.18
On Fri, 23 Apr 2004, Steven Palm wrote: Well, using gcc-3.3_branch and UnixWare 7.1.0, I tried the dev release 4.1.1-alpha off the mysql site to see if I would have better luck You need the latest bk http://dev.mysql.com/doc/mysql/en/Installing_source_tree.html You can run bk under the LKP. That is how I do it. Many patches and fixes are in the bk. Some are necessary for UnixWare 7.1.X. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
I must have done a poor job at explaining this. I already have all those primary keys, etc. If you are required to do a full table scan on the table, as I am in my example, please explain to me how an index even matters in that case. There is _no_ where clause in the query so how will an index help? :) Now I have been benchmarking creating a CKEY combined key column that just basically is a concat(month_day, ',', bucket, ',', src). I made that a char(44) column and make that the primary key. Things seem faster due to use only one row for the primary key instead of 3, as well as a less complex group by. Preliminary results show the CKEY to be 50% faster on the particular query I'm using. 1) Yes, it does have to calculate floor(bucket / 3) but that is fairly in expensive call on a per-row basis, one would think. 2) I already have that index. It's a Primary Key(month_day, src, bucket). I'm still confused on how the index will speed it up on the source table side. We are NOT I/O bound. Looks more like cpu bound to me. Mysql uses 25% cpu on the solaris which is 1 entire cpu on a 4 processor machine. I'll give the order by NULL a shot Cliff Jigal van Hemert [EMAIL PROTECTED] writes: insert into new_table select month_day, floor(bucket/3) as bucket, date, src, avg(value) as value from source_table group by month_day, bucket, src; Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort Query OK, 11495208 rows affected (4 hours 47 min 21.01 sec) IMHO two things slow you down: 1) floor(bucket/3) as bucket. This means that MySQL has to calculate this value for each record to be able to use it as a grouping value. If you always use this same expression for grouping, it might be useful to have a column with the precalculated value. 2) there's only one table involved, so only one index will be used. If you had an index that contains month_day, bucket and src it would speed up things perhaps. Furthermore, it depends on the speed of the disks, your configuration, memory configuration and use how fast it will go. But explain indicates that MySQL needs to copy the data into a temporary table and use filesort to order and group things. Maybe it will help to surpress the automatic sorting that is done by MySQL because of the GROUP BY, by adding ORDER BY NULL? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
display
hi, I want to know if there is a way to change the display of the mysql query results. itz confusing to see if you have a lot of fields in the table. and if the string is a long string for a particular string there are lot of lines that are being printed. Liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: display
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2004-04-24 19:40 +0200]: I want to know if there is a way to change the display of the mysql query results. If you're under GNU/Linux, type \P less -S at the mysql prompt. This sets the pager to less, which allows you to use the four cursor keys for scrolling though the results. Another idea would be typing \G instead of ; at the end of each statement, which prints the columns as lines. Type help at the mysql prompt to learn more about these internal commands. -- Johannes Franken MySQL Certified Professional mailto:[EMAIL PROTECTED] http://www.jfranken.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trying to upgrade
Hello, I have seen lots of these same errors but no real solutions. Hopefully you can help me out I am trying to upgrade an existing Mysql install from mysql-3.23.58-1.9 to the latest mysql-4 When I try to run the upgrade RPM I get rpm -Uv MySQL-server-4.0.18-0.i386.rpm warning: MySQL-server-4.0.18-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3 libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2 The solution I saw was to rpm -e mysql* Does than not install everything? This is a working db Thanks for any suggestions Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SLOW 22million rows, 5 hour query?
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html Do not use DELAYED with INSERT ... SELECT. With respect to the table structure...can you explain how when you have to read every single row regardless how the structure (assuming you are going down the path of idexes) affects the query? Cliff Donny Simonton [EMAIL PROTECTED] writes: Use insert delayed, and you will cut your time in half. At least with my experience. But also how long does it actually take to run the query itself. Giving a summary explain doesn't help much. You really need a table structure that the select is using and a full explain. Donny -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 6:02 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: SLOW 22million rows, 5 hour query? On 23 Apr 2004, at 9:48 pm, [EMAIL PROTECTED] wrote: Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort The filesort is a giveaway. Can you increase the sort buffer size so that the sort can happen in memory rather than having to use a file to sort? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- 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]
RE: SLOW 22million rows, 5 hour query?
Cliff, still no explain still not table structure. Until that happens enjoy the 5 hour club. Donny -Original Message- From: Cliff Daniel [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 6:41 PM To: Donny Simonton Cc: 'Tim Cutts'; [EMAIL PROTECTED] Subject: Re: SLOW 22million rows, 5 hour query? http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html Do not use DELAYED with INSERT ... SELECT. With respect to the table structure...can you explain how when you have to read every single row regardless how the structure (assuming you are going down the path of idexes) affects the query? Cliff Donny Simonton [EMAIL PROTECTED] writes: Use insert delayed, and you will cut your time in half. At least with my experience. But also how long does it actually take to run the query itself. Giving a summary explain doesn't help much. You really need a table structure that the select is using and a full explain. Donny -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 6:02 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: SLOW 22million rows, 5 hour query? On 23 Apr 2004, at 9:48 pm, [EMAIL PROTECTED] wrote: Relevant `explain` details: Full table scan: 22,371,273 rows, Using temporary; Using filesort The filesort is a giveaway. Can you increase the sort buffer size so that the sort can happen in memory rather than having to use a file to sort? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
