RE: re-ordering rows
What Rick said is absolutely correct and you probably are obsessing about something that doesn't matter. But I would venture you are using an auto-number field as the primary key when you could easily change it to a function something similar to: set ID = MAX(ID) + 1. -Original Message- From: Bryan McCloskey [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 14, 2002 4:03 PM To: [EMAIL PROTECTED] Subject: RE: re-ordering rows You're right, it's not important how the data is stored inside the database. I was just hoping that there would be a way to set a default order so that I wouldn't have to write a cumbersome ORDER BY phrase every time I wanted to see the data. I thought that perhaps indexes could accomplish this, somehow by telling them to re-index the column, but perhaps not. -b --- Rick Emery [EMAIL PROTECTED] wrote: Why is the internal order important? When SELECTing, the internal order is of no importance to MYSQL. It does not speed-up the query or access. When discussing relational database systems, all that matters is the order of output. __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - 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 - 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 + Access + MyODBC + LARGE Tables
I'm not an expert on MySQL or can address any of the tuning issues you bring up. I will say this, you are not totally correct in how ACCESS is retrieving records. VB and Microsoft Jet retrieve dynasets which is basically the primary key in its entirety. When you move to the next screen ACCESS retrieves the attribute values related to the primary key. The dynaset is stored in RAM and if there is none available it will go to virtual memory. Thus, if you have millions of records ACCESS is going to retrive millions of KEY_ID and try and store them within the local machine's Volitile memory space. I'm sure you can see the problem here because you are also trying to run an operating system and at least one application at the same time. The trick is to only bring the dynaset accross the network you need to retrieve and use MySQL's indexing processing power to get the records. I have had success with tables with millions of records in ACCESS on a PC. Of course, if I tried to open and browse through the table in datasheet view it would drag down the system and take 20 mins just to open the table with the first set of records. However, if I sent a record limiting query to the backend the only records sent over the network would be the ones requested. I don't think I ever ran into a situation where an end user needed to browse through a table with a million records. Another word to the wise about ACCESS. Make sure you split your database into a back-end and front end so the user is actually working off the front end located within their local drivespace. You would put linked and local tables in the back-end and forms and reports in the front. This way if there is a local system lock it will only trash the local application and not the network application. You can see the issue here as well. The simple act of someone killing the cpu power during a write operation and the phone will be ringing because no one can access the database application...if you don't have a back-up you might just be writing the thing all over again. I know you probably are aware of this issue but it didn't hurt to say it (*_*). I hope this helped at least a little. -Original Message- From: Bill Adams [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 14, 2002 6:05 PM To: MySQL List; MyODBC Mailing List Subject: MySQL + Access + MyODBC + LARGE Tables Monty, Venu, I hope you read this... :) I really, really want to use MySQL as the database backend for my datawarehouse. Mind you I have played around with merge tables quite a bit and know that MySQL is more than up to the task. There are numerous (not necessarily cost related) reasons as to why MySQL is better for my application. If it were just me, it would be a slam-dunk as I only use perl, etc. to extract data from the database. However most of my users use MS Access as a front end and extraction tool. When pulling datasets from a database, Access tries to be smart and if there is what it thinks is a primary key on a table, it will extract the values of the primary key for the matching records and then re-query the table with a parameterized query to get the rest of the values. This is true in both the case where a user tries to view a table or runs a simple query. Taking a simple case of the user opening the table in data sheet view (if this is solved, the other cases will be solved too), the following happens -- okay, this is a bit simplified, see my message Large Datasets w/Access for better background: http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaokcknfmaldpo kp -- Access opens a statement handle (#1) and queries the table for the primary key values. E.g. It would pass SELECT idx FROM TABLE. Note that it only cares about getting a partial list here. I.e. if the screen only shows 10 records, Access only cares about 10 primary key values. -- Access opens a second statement handle (#2) without closing the first handle and then gets the values in a parameterized query. E.g.: SELECT a, b, idx FROM table WHERE idx=? OR idx=? It then pulls the records it cares about with this statement and closes the statement. -- If, say, the user presses page down, [I think] access then gets the next set of primary key values from statement handle #1, sets up another prepared query and gets the values as above. MyODBC, as compiled today, uses mysql_store_result to get records. This is fine for reasonably sized tables. However, if the table has millions of records, writing the results to a temporary table has many detrimental effects, e.g.: Access seems to hang from the user's perspectiv, Access crashes because there are too many records for it to handle at once (data requirements to great); MySQL creates HUGE temporary tables or bombs if SQL_BIG_RESULT was not set. So in the case of a very long table, it is important to use mysql_use_result instead. This makes it so that results are returned right away and eases the load on all programs
RE: ORDER BY an ABSolute value
Did you try SQRT(id)? Should rank things the way you want if it's legal (*_*) -Original Message- From: David Mackay [mailto:[EMAIL PROTECTED]] Sent: Monday, February 11, 2002 7:08 PM To: 'David Turner'; David Mackay Cc: '[EMAIL PROTECTED]' Subject: RE: ORDER BY an ABSolute value Thanks for your quick response Dave, Have tried this, but no bannana... I get: You have an error in your SQL syntax near 'abs(id)' at line 1 Seems a not-valid thing to do these days... Is there a work around? Dave from Oz select id from table_name order by abs(id); Dave Dundee! On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote: G'Day folks, New to PHP/MySQL. Want to order the results of a SELECT by their 'absolute' value, not their sign. So regardless of whether it's +37 or -37, they are both 37 and thus both come between 38 36, regardless of whether they're + or -. eg. 100 -99 -92 91 72 -38 37 -37 etc... I have the order DESC but it puts 100 first, and -100 last, (that's what you'd usually want, but I need the extremes grouped at one end, down to the middle point zero..). In my fantasy world I could do a ORDER BY ABS(number) DESC If there was any other way to achieve the same result that would be just dandy... I bet this is obvious. Thanks for your time, David Mackay - 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 - 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 - 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 - 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: Problems with a join...
Try SELECT pd.ANSWER_TEXT, pr.*, COUNT(*) as NUM FROM pollresponse left join polldata on pd.QUESTION_ID = pr.QUESTION_ID WHERE pd.VOTE_ID = pr.RESPONSE GROUP BY pr.RESPONSE Something like that will work better...probably you have to massage it a little. Note that you were not getting any results because you had a full join when you should have had a LEFT join. (that's a hint to the above). There has to be a record in both tables to get results. You can read about it here: http://www.mysql.com/doc/J/O/JOIN.html You might also consider the following data model POLL POLL_ID POLL_description ANSWER POLL_ID ANSWER_ID ANSWER_description RESPONSE RESPONSE_ID USER_ID (if you are tracking users) POLL_ID ANSWER_ID Seems like that is normalized. You'll find the indexing more efficient. -Original Message- From: Chad Day [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 3:35 PM To: mysql Subject: Problems with a join... I'm trying to write a polling application and display the results on an admin screen.. my goal is to show all the answers for the poll questions and display the # of votes for each. The query I have to do this is: select pd.ANSWER_TEXT, pr.*, COUNT(*) as NUM from ft_pollresponses as pr right join ft_polldata as pd on pd.QUESTION_ID = pr.QUESTION_ID and pd.VOTE_ID = pr.RESPONSE GROUP BY pr.RESPONSE but the problem is that if an answer has 0 votes, it doesn't show up in the query results.. I know I'm doing something wrong with the join, but an unsure what.. can anyone help? Below are my 2 table schemas. CREATE TABLE ft_pollresponses ( POLL_ID int(11) DEFAULT '0' NOT NULL, QUESTION_ID int(11) DEFAULT '0' NOT NULL, RESPONSE varchar(200) NOT NULL, KEY POLL_ID (POLL_ID), KEY QUESTION_ID (QUESTION_ID) ); CREATE TABLE ft_polldata ( POLL_ID int(11) DEFAULT '0' NOT NULL, QUESTION_ID int(11) DEFAULT '0' NOT NULL, ANSWER_TEXT char(175) NOT NULL, VOTE_ID int(11) DEFAULT '0' NOT NULL auto_increment, PRIMARY KEY (VOTE_ID) ); Any help is much appreciated... Thanks! Chad - 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 - 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
Table appears in multiple joins returns null column
I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname - 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 - 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: Table appears in multiple joins returns null column
DL, Thank you very much for the response I really appreciate it. I did follow your suggestions and then simplified things a bit more. I posted the next two queries below so you could see where the query goes wrong. I guess I'm not really looking for a 'quick-fix' solution but rather I am missing some high-level understanding of the relational schema and SQL. Perhaps you are telling me what is wrong and I am not seeing it. Sorry if that is the case. I did figure out the multiple join syntax. The query is below. DESCRIBE SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES INNER JOIN POINTFUND ON TYPES.TYPE_ID = POINTFUND.TYPE_ID INNER JOIN PARTY ON PARTY.PARTY_TYPE = TYPES.TYPE_ID INNER JOIN RACES ON TYPES.TYPE_ID = RACES.TYPE_ID INNER JOIN SCHEDULE ON SCHEDULE.SCHEDULE_ID = RACES.SCHEDULE_ID ORDER BY SCHEDULE.date, PARTY.lname table type possible_keys key key_len ref rows Extra POINTFUND ALLTYPE_IDNULLNULL NULL 2 Using temporary; Using filesort TYPES eq_ref PRIMARYPRIMARY 4POINTFUND.TYPE_ID 1 PARTY refPARTY_TYPE PARTY_TYPE 4TYPES.TYPE_ID 3 RACES ALLSCHEDULE_IDNULLNULL NULL 5 where used SCHEDULE eq_ref PRIMARY, SCHEDULE_ID, SCHEDULE_ID_2 PRIMARY 4RACES.SCHEDULE_ID 1 I've tried dozens of combinations of different types of joins and still haven't come to the correct results. After looking at the description of these queries I have come to believe the issue lies not in the query itself but how MySQL is using the index. It seems when the key field in the description is NULL that points to where the RDBMS is having trouble pulling out the correct records. All of the ID fields are int(11) so I don't think there should be a datatype problem. From the example below I am OK until POINTFUND is added to the query and then things go awry where I can't seem to pull the POINTFUND.description field I need in the query. I'll continue to plug away at this and see if I can do something with the index...I have already dropped and rebuilt every index in the DB! Thought someone might have some more info for me. Again, thank you for any time you spent on this I appreciate the value of your time. _ Thanks for the responses. The below query works fine: SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, FROM TYPES, RACES, SCHEDULE, PARTY, WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID AND RACES.TYPE_ID = TYPES.TYPE_ID AND ORDER BY SCHEDULE.date, PARTY.lname The below query returns no records: SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES, RACES, SCHEDULE, PARTY, POINTFUND WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID AND RACES.TYPE_ID = TYPES.TYPE_ID AND POINTFUND.TYPE_ID = TYPES.TYPE_ID ORDER BY SCHEDULE.date, PARTY.lname Here are descriptions of the tables in question. DESCRIBE TYPES Field Type Null Key Default Extra TYPE_ID int(11) PRI 0 typeshort char(3) typelong varchar(45) category tinytext YES NULL DESCRIBE POINTFUND; Field Type Null Key Default Extra POINTFUND_ID int(11) PRI NULL auto_increment TYPE_ID int(11) 0 begin date -00-00 end date -00-00 description varchar(255) There are values in POINTFUND.TYPE_ID that match TYPES.TYPE_ID. The obvious solution to this is to create a query that joins POINTFUND and TYPES and then use that query in the above query insead of the POINTFUND table (I hope that makes sense). Unfortunatelly there doesn't seem to be a mechanism in MySql to do this. Any other suggestions? -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 09, 2002 5:42 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Table appears in multiple joins returns null column Keith, I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would have told you. The answer may lie in the definitions of the races, types,
RE: Columns
Sounds like an UPDATE: http://www.mysql.com/doc/U/P/UPDATE.html UPDATE TABLE SET columnname1 = columnname2 where KEY = KEY Something like that probably. If it was me I would copy the whole table so I had a backup...UPDATES and DELETES can be destructive if writting improperly (*_*) -Original Message- From: Max Mouse [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 4:11 PM To: [EMAIL PROTECTED] Subject: Columns Hey all, Is it possible to copy the contents of one column to another column using mySQL? I just changed my table structure by adding a few more columns and I need to be able to move the data from the original column to 4 new columns and then drop the orginial. I know that the proper query for sql is that I added with ALTER and remove with DROP. But I can't find anything that would allow me to move the data from one column to another. Anything I can do? Max - 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 - 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 - 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: How to access MySql using Perl. Help
Read through the man doc lcalero gave you it tells everything you need to
know. Below is a basic example to help get you started:
#!/usr/bin/perl
use CGI;
use DBI;
$hostname = 'www.hostname.com';
$database = 'nameofdatabase';
$user = 'username';
$password = 'password';
$driver = 'mysql';
$dsn = DBI:$driver:database=$database;host=$hostname;
my $dbh = DBI-connect($dsn, $user, $password) or die Cant connect to
the DB:
$DBI::errstr\n;
my $sth = $dbh-prepare(SELECT attributes from entities);
$sth-execute();
my $something;
while (my @row = $sth-fetchrow_array()) {
$something .= qq| $row[2]$row[1]etc.\n|;
}
#each row is respective to the row returned from the query with the first
being row 0
$sth-finish;
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 12, 2002 4:11 AM
To: Andy Cheng
Cc: [EMAIL PROTECTED]
Subject: Re: How to access MySql using Perl. Help
I am new to MySql. I have MySql installed in my Linux PC. How do I
connect
to MySql using Perl script and assign the result of a query to an array?
Where could I find a sample code? Thank you.
man DBD::mysql
Cheers.
--
Luis Calero Muñoz
$email{luis} = '[EMAIL PROTECTED]'
$who{luis} = 'sysadm at ociojoven dot com'
-
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
-
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: ORDER BY an ABSolute value
Did you try SQRT(id)? Should rank things the way you want if it's legal (*_*) -Original Message- From: David Mackay [mailto:[EMAIL PROTECTED]] Sent: Monday, February 11, 2002 7:08 PM To: 'David Turner'; David Mackay Cc: '[EMAIL PROTECTED]' Subject: RE: ORDER BY an ABSolute value Thanks for your quick response Dave, Have tried this, but no bannana... I get: You have an error in your SQL syntax near 'abs(id)' at line 1 Seems a not-valid thing to do these days... Is there a work around? Dave from Oz select id from table_name order by abs(id); Dave Dundee! On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote: G'Day folks, New to PHP/MySQL. Want to order the results of a SELECT by their 'absolute' value, not their sign. So regardless of whether it's +37 or -37, they are both 37 and thus both come between 38 36, regardless of whether they're + or -. eg. 100 -99 -92 91 72 -38 37 -37 etc... I have the order DESC but it puts 100 first, and -100 last, (that's what you'd usually want, but I need the extremes grouped at one end, down to the middle point zero..). In my fantasy world I could do a ORDER BY ABS(number) DESC If there was any other way to achieve the same result that would be just dandy... I bet this is obvious. Thanks for your time, David Mackay - 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 - 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 - 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
