Re: Query: Order for the Earliest Latest date
In addition to the id_Site, you also need to grab the MAX(Time) so you have something to sort by. This requires a little trick known as a groupwise maximum. See http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html for an explanation and some examples. Greg Fortune On Tuesday 24 January 2006 09:06, Dan Baker wrote: [GENERAL INFO] I have two tables I'm working with. One table (Sites) contains contact information for every customer site that we deal with. The other table (Incidents) contains all the support calls we've made. [QUERY] I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. I'm using: SELECT DISTINCT id_Site FROM Incident WHERE Time = $date1 AND Time = $date2 ORDER BY Time DESC Which gives me a list of sites that had a support incident between the dates, but doesn't really sort them correctly. It simply orders them by who had the earliest support call. I'm looking for the site who's LAST support call is the EARLIEST. [Incident TABLE] Field Type Null Default Links to Comments MIME id int(11) No Time int(11) No 0when call came in text/plain Description varchar(100) No brief description Notes text No operator notes id_Site int(11) No 0 site - id ... Thanks for any pointers. DanB pgpQ7novDk8tC.pgp Description: PGP signature
Re: problems with old isam tables
That looks a little odd. Do isam tables have a .myi file for each table that stores the indexes? If so, yours appears to be missing. I would suggest you use mysqldump to dump the table, change the ENGINE= section in the resulting dump file, and then reload the table and data from that dump file. When upgrading between major versions (4.0, 4.1, 5.0, 5.1, etc), it's always wise to make a full backup of the data files and a full backup of the database using mysqldump. The output from mysqldump is just SQL so it is always the safest route if you run into problems. Greg Fortune On Wednesday 11 January 2006 13:39, Anne Ramey wrote: I have a problem. I had to upgrade to 4.1 yesterday for an application, and now I can't convert my old isam tables to myisam: ALTER TABLE codes TYPE = MYISAM; ERROR 1017 (HY000): Can't find file: 'codes.MYI' (errno: 2) and I can't use phpmyadmin because there are isam tables. Is there any way to save the data and fix this? I know it should have been done when upgrading to 4.0, but for some reason it wasn't. mysql 4.1, redhat ES3 -- Anne pgp323tYrtfbt.pgp Description: PGP signature
Re: problems with old isam tables
Also, you may find that check table/repair table are able to fix the problem. See http://dev.mysql.com/doc/mysql/en/Table_maintenance.html for more information. Greg On Thursday 12 January 2006 08:28, Greg Fortune wrote: That looks a little odd. Do isam tables have a .myi file for each table that stores the indexes? If so, yours appears to be missing. I would suggest you use mysqldump to dump the table, change the ENGINE= section in the resulting dump file, and then reload the table and data from that dump file. When upgrading between major versions (4.0, 4.1, 5.0, 5.1, etc), it's always wise to make a full backup of the data files and a full backup of the database using mysqldump. The output from mysqldump is just SQL so it is always the safest route if you run into problems. Greg Fortune On Wednesday 11 January 2006 13:39, Anne Ramey wrote: I have a problem. I had to upgrade to 4.1 yesterday for an application, and now I can't convert my old isam tables to myisam: ALTER TABLE codes TYPE = MYISAM; ERROR 1017 (HY000): Can't find file: 'codes.MYI' (errno: 2) and I can't use phpmyadmin because there are isam tables. Is there any way to save the data and fix this? I know it should have been done when upgrading to 4.0, but for some reason it wasn't. mysql 4.1, redhat ES3 -- Anne pgpvBPtiiNNqf.pgp Description: PGP signature
Re: Make a report like this...
You might try --xml or --html with a different separator. Greg -- Greg Fortune Enterprise Systems Eastern Washington University Phone: 509-359-6690 Email: [EMAIL PROTECTED] On Wednesday 20 July 2005 03:28 pm, Dan Bolser wrote: +--+---+++-+ | G_ID | TOTAL | G2 | NR | ASSEMBLY_LISTING| +--+---+++-+ | 1132 |34 | 1 | 1 | 1bf3-1,1bgj-1,1bgn-1,1bkw-1,1cc4-1, | | | | ||| 1cc6-1,1cj2-1,1cj3-1,1cj4-1,1d7l-1, | | | ||| 1dob-1,1doc-1,1dod-1,1doe-1,1ius-1, | | | ||| 1k0i-1,1k0j-1,1k0l-1,1pbb-1,1pbc-1, | | | ||| 1pbd-1,1pbe-1,1pbf-1,1pdh-1,1phh-1, | | | ||| 1pxa-1,1pxb-1,1pxc-1,2phh-1 | +--+---+++-+ | 1793 |10 | 1 | 1 | 1jdx-1,2jdw-1,2jdx-1,3jdw-1,4jdw-1, | | | | ||| 5jdw-1,6jdw-1,7jdw-1,8jdw-1,9jdw-1 | +--+---+++-+ Hello, I made the above report by hand after using a GROUP_CONCAT query. Is there any way to make this kind of format automatically in MySQL? It seems it would be a nice feature. Dan. pgpOWQjoXduTN.pgp Description: PGP signature
Re: remove trailing character
How about UPDATE table set email = TRIM(TRAILING '' FROM email) http://dev.mysql.com/doc/mysql/en/string-functions.html Greg Fortune On Thursday 10 March 2005 03:29 pm, Scott Haneda wrote: I managed to mess up and email storage addresses are in the format of [EMAIL PROTECTED] How I can strip off the , it does not exist on all, only some. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join Limits
Any chance of condensing some of the flag fields into bit fields? Alternatively, can you represent the variables as (rule id, variable name, variable value) in a single table rather than using lots of columns/tables? Greg Fortune On Monday 07 March 2005 10:31 am, Kevin Cowley wrote: Unfortunately both limits are getting in our way. We have approximately 32,000 variables scattered across a number of tables that we need to convert to bitmaps. The problem is that about 1500 of these variables need to go in a single bitmap hence the problems with the 1024/64 column/table limit. If you're correct and its using a bitmap in the optimizer, then it means its possible to extend the value by replacing the bitmap with a structure and a clever set of functions. I've used this technique in another application to overcome a 32 bit limit - its probably going to be a bastard of a job to rewrite the relevant parts of MySql though. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Eric Bergen [mailto:[EMAIL PROTECTED] Sent: 07 March 2005 18:16 To: Kevin Cowley Cc: mysql@lists.mysql.com Subject: Re: Join Limits The join table limit in MySQL is dictated by the arch that's running on. 32 tables for 32bit and 64 tables for 64bit (Somebody correct me if I'm wrong). I believe this is due to using a bitmap inside the join optimizer to keep track of tables. 64 tables is a very hefty query it makes me think that maybe you are doing something wrong when designing your application to need a join that big. When the optimizer optimizes a query it checks every possible execution path through every table so the more joins you have the more paths the opimizer has to check to find the optimal one (This isn't entirely true but close enough for my argument here). Needing more than 1024 columns in a table also seems like bad design. Maybe you can detail more of what you are doing and why you need so many columns -Eric On Mon, 7 Mar 2005 12:21:52 -, Kevin Cowley [EMAIL PROTECTED] wrote: Does anyone know if there is a method of circumventing or changing the default join limits of 64 tables or 1024 columns? We're running Mysql 4.1.4 using MyISAM tables Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk http://www.alchemetrics.co.uk Email: [EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net *** *** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. *** *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List
Re: what is select_limit (and what is it used for)?
It only affects the client and only when the --safe-updates, --i-am-a-dummy, -U flag is used. I assume it just places an explicit limit clause of LIMIT 1000 on the end of any select statement run from the client. It's meant to keep less experienced users from running a couple of cross product joins that output millions of lines of data. Greg Fortune On Monday 07 March 2005 10:48 am, Caron, Christian wrote: Hi, the default select_limit is set to 1000. What does this mean and how can it affect a server? Is there some information out there about this variable? I can't find anything except a one line description on the MySQL site... - Christian Caron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
Remember, a low cardinality index will possibly be ignored by the optimizer and an index on month will never have a cardinality of more than 12. For testing purposes, you might try added a column for month and populating it off your current data. update the_table set the_field=MONTH(the_field) Then, add an index on that column and test your queries against the new index. I'd be surprised if you saw much increase in speed, especially as your data set grows. Greg On Wednesday 09 February 2005 09:17 am, Gabriel B. wrote: Short Version: is there any way to make an index for a date field that appears in the WHERE as a MONTH() argument? I have a table with some hundreds of thousands of rows already, and now i have the need to show upcoming birthdays to some users. the query uses WHERE MONTH(birthday).. the `birthday` field is of date (-00-00) type It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. So, is there any way to make an index for that kind of query, or i must re-estruct the table to have a month field? Thanks! Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need more info about currently running queries
Yep, mysqladmin can give the same info. As noted in my original message, I need much more detailed info. The crux of the problem is that I need to run a query that could take several hours when it's using indexes correctly, but it is not working correctly so it takes longer than I've been willing to wait (about 14 hours). I need to make changes to indexes and run the query again, but I don't want to wait hours until I know whether the change was effective or not. I really need a way to find out how far the query has progressed after 15 minutes so I can calculate how much improvement the changes made. Greg On Tuesday 21 December 2004 02:25 am, Gleb Paharenko wrote: Hello. Using SHOW PROCESSLIST you can just check if your query running, or is waiting for some lock. For more info, you can run ps axm in shell and look for the thread state, but that's more related to the kernel stuff. See: http://dev.mysql.com/doc/mysql/en/SHOW_PROCESSLIST.html Greg Fortune [EMAIL PROTECTED] wrote: Is there anyway to get additional information about a query that is currently running? I've got some performance problems I'm trying to analyze while loading large data sets and I'm running a test query, but I don't have any idea how far the query has progressed. The test query is a count(some_field) with the JOINs that I need and I'd like to know how many rows it has counted or how many rows it has visited from each table, etc, etc, etc. I know ahead of time that the result should be about 2.6 million and am just trying to get timing data. Is there any way to get at this kind of info? Greg -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need more info about currently running queries
Is there anyway to get additional information about a query that is currently running? I've got some performance problems I'm trying to analyze while loading large data sets and I'm running a test query, but I don't have any idea how far the query has progressed. The test query is a count(some_field) with the JOINs that I need and I'd like to know how many rows it has counted or how many rows it has visited from each table, etc, etc, etc. I know ahead of time that the result should be about 2.6 million and am just trying to get timing data. Is there any way to get at this kind of info? Greg -- Greg Fortune Enterprise Systems Eastern Washington University Phone: 509-359-6690 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
