Re: Optimize Table usage
On Sat, 4 Jan 2003 21:25:03 -0500, Dan Cumpian wrote: 1) Do I: Query.ExecSQL or Query.Open to execute the OPTIMIZE TABLE TableName command? From Delphi help: Use ExecSQL to execute queries that do not return a cursor to data (such as INSERT, UPDATE, DELETE, and CREATE TABLE). 2) The queries are returning instantly and when the program tries to run OPTIMIZE TABLE on the next table, I am getting a commands out of sync I saw this when using optimize table via a GUI client, not sure where it comes from. In my case it wasn't a factor of using it on multiple tables. - Steve Yates - BUS STOP: Where the I/O gets off. ~ Taglines by Taglinator - www.srtware.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
Re: Select Date Help
On Thu, 2 Jan 2003 14:27:25 -0800, Max Clark wrote: select * from table where date = '2003-01' aside from ...where (date='2003-01-01' and date = '2003-01-31') I think you could use ...where left(date,7) = '2003-01' http://www.mysql.com/doc/en/Date_calculations.html - Steve Yates - To know recursion, you must first know recursion. ~ Taglines by Taglinator - www.srtware.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
Fwd: Re: RE: Trouble converting SQL from Access
On Fri, 27 Dec 2002 13:11:42 -0500, Asendorf, John wrote: SELECT Dealers.*, SQRT(POW((2285-Zips.North),2)+POW((4760-Zips.West),2)) AS Distance FROM Dealers INNER JOIN Zips ON Dealers.Zip = Zips.Zip ORDER BY POW((2285-Zips.North),2)+POW((4760-Zips.West)),2) Off the top of my head have you tried not calculating the order by field again? Something like: SELECT Dealers.*, POW((2285-Zips.North),2)+POW((4760-Zips.West),2) as L, SQRT(L) AS Distance FROM Dealers INNER JOIN Zips ON Dealers.Zip = Zips.Zip ORDER BY L (or should it be order by Distance?) - Steve Yates - 62% of those polled felt polls asked trivial questions. ~ Taglines by Taglinator - www.srtware.com ~ sql - 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: Re: No descending index ?
One trick I have used in the past in other databases is to create your own descending index. If your field is say fieldA, when you enter a row into the table take the value of fieldA and subtract from 0, then put that in fieldB. Index fieldB and you now can order rows descending. - Steve Yates - Can't I have just a little bit of peril? ~ Taglines by Taglinator - www.srtware.com ~ sql,query - 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 and PHP question
On Wed, 18 Dec 2002 16:05:35 -0500, C. Reeve wrote: I can not use two mysql queries in the same PHP file Please post code snippets, this is possible. - Steve Yates - Burger Borg: We do it our way; yours is irrelevant. ~ Taglines by Taglinator - www.srtware.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
Re: MySQL and PHP question
On Wed, 18 Dec 2002 22:54:53 -0500, C. Reeve wrote:
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$num1 = $line['number'];
}
Loop through the result and return the value from the last row?
Did you want select sum(number) as SubTotal1 from table1 where
userid=$user1?
$query select number from table2 where userid=$user2;
$result = mysql_query($query) or die(Query failed);
You re-used the same variable $result. Offhand I would try
using a second variable, and/or using mysql_free_result() in between.
- Steve Yates
- My hard disk is full! Maybe I'll try this message section thing.
~ Taglines by Taglinator - www.srtware.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
Re: Primary key question
On Tue, 17 Dec 2002 19:15:08 +0100, Serrand Patrice wrote: Does MySQL automatically create index on primary key ? Yes. See http://www.mysql.com/doc/en/CREATE_TABLE.html - Steve Yates - Antonym: The opposite of the word you're searching for. ~ Taglines by Taglinator - www.srtware.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
Re: Fwd: Re: Bug in auto_increment
On Sun, 15 Dec 2002 17:38:49 -0600, Paul DuBois wrote: INSERT INTO gebaeude_daten VALUES (0, 'Bank Stufe 1', 1000, 30, 900, 1); What happens if you use a value of NULL instead of 0? Inserting a field with 0 tells the table to generate a value on its own, like it's supposed to. From your post you appear to be trying to assign a value to the auto_increment which defeats its purpose. I would suggest tracking the current ID for this table in another table, then inserting it yourself. Remember to lock the second table to prevent two sessions from using the same number. Every time I change the table definition with ALTER, the 0 becomes the next auto-index. The user comments on this page point out that setting a value for an auto-increment field with ALTER TABLE will reset the counter to the provided value. http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html - Steve Yates - Does Microsoft mean small and limp? ~ Taglines by Taglinator - www.srtware.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
Re: Field type conversion question
On Thu, 12 Dec 2002 08:59:45 -0500, Brad Harriger wrote: like to have a field in Events that will store multiple names with both fields from EmpTable concatenated into one string (i.e. FirstName LastName) What is the most efficient way to do this in MySQL? One approach is an EventAttendance table that has two fields (eventnum, employeenum). Then multiple employees can attend one event, and there are no long strings running around. - Steve Yates - If you smoke after sex, you're doing it too fast. ~ Taglines by Taglinator - www.srtware.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
Re: import size
On Thu, 5 Dec 2002 10:16:16 -0700, Sarah Killcoyne wrote: I've found that using the local infile import query on files that are several mb in size takes up to a min per mb I've posted a couple times recently on a Delphi project I wrote to upload data from a client's Access database (3 tables) to the web on demand. It is about 4 MB in size and though it does take time it takes way less than 4 minutes. I did find out that using a query made a huge difference over using a table (i.e. editing the MySQL table directly), and using the REPLACE...VALUES syntax with multiple value entries made a huge difference too. I ended up iterating through the table like this (pseudocode): while (still data left) i = 1 querystr = 'replace delayed into(cols) values ' while (still data left and i 20) querystr = querystr + (data) i = i + 1 end execute query end DELAYED made very little difference in the upload speed, but seemed to allow use of the web database while the upload was happening. Be careful about the size of the data though...one of the three tables had many columns including a text field and I found that using i 20 would result in a string that was too long. - Steve Yates - Between two evils, always pick the one you haven't tried. ~ Taglines by Taglinator - www.srtware.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
Re: Questions Compressed Tables Indexs
On Thu, 05 Dec 2002 15:36:37 -0500, Michael She wrote: As for indicies is it better to create a single index per column, or an index for a set of columns. For example, if I go: SELECT * FROM SOMETABLE WHERE A = X and B = Y. Is it better to create a X AND Y index, or two separate indicies? If you need to ever query on the B field alone and use an index, it will need its own index. Queries on either field A or fields A,B together will use an index on A,B. Queries can use one index so in your example an index on A,B I suspect should be faster. http://www.mysql.com/doc/en/MySQL_indexes.html http://www.mysql.com/doc/en/Multiple-column_indexes.html - Steve Yates - When you're great people sometimes mistake candor for bragging. --Calvin ~ Taglines by Taglinator - www.srtware.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
Re: access-mysql
On Wed, 4 Dec 2002 14:39:01 -, Sandeep Murphy wrote: method for converting a huge database i hv in Access xp to MySQL ?? I just wrote a short program in Delphi (using MyODBC) to pull information from an Access MDB and update a MySQL table. Worked fine. I'm sure one could accomplish the same thing in Access natively but I'm far better versed in Delphi. One note...at least in Delphi, uploading from the desktop is FAR faster using a query and update/insert than using a table, especially since one can use multiple VALUES per INSERT query. - Steve Yates - Line noise provided by Ameritech! ~ Taglines by Taglinator - www.srtware.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
Re: help with picking the right index(s)
On Tue, 03 Dec 2002 12:47:28 +1100, Justin French wrote: my php script looks for a row matching this year (2002), this month (12) and this page (something.php) It would seem like one index on year/month/page would be appropriate then. It could be used for both lookups and for reporting (a query for 2002/12/* would still use this index). - Steve Yates - Detour: The roughest distance between two points. ~ Taglines by Taglinator - www.srtware.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
Re: Select, mysql_fetch_array, PHP question
On Tue, 3 Dec 2002 08:38:59 -0500, Beauford.2003 wrote:
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $item; }
Try echo $line['item'];
- Steve Yates
- Any sufficiently advanced magic looks like technology.
~ Taglines by Taglinator - www.srtware.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
Re: DateTime Calculations
On Tue, 03 Dec 2002 10:45:57 -0600, Peter Abilla wrote: (Column Two - Column One) = Total Minutes Assuming this doesn't work :) perhaps select UNIX_TIMESTAMP(col2) - UNIX_TIMESTAMP(col1) as TimeDiff This should give you an answer in seconds. [sql] - Steve Yates - File not found. Should I fake it? (Y/N) ~ Taglines by Taglinator - www.srtware.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
Re: Front end for MySQL databases
On Wed, 27 Nov 2002 07:56:42 -0800, Todd Cary wrote: Most of my experience has envolved writing a front end in Delphi and using ODBC I just wrote a short program in Delphi (using MyODBC) to pull information from an Access MDB and update a MySQL table. Worked fine. Uploading from the desktop is FAR faster using a TQuery and update/insert than using a TTable, especially using multiple VALUES per query. - Steve Yates - Patience: A virtue that carries a lot of wait. ~ Taglines by Taglinator - www.srtware.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
Re: SQL Row Counting in MySQL
On Tue, 26 Nov 2002 15:01:08 -, Tom Place wrote: a simple way in MySQL to only select the first x rows See LIMIT: http://www.mysql.com/doc/en/SELECT.html - Steve Yates - If at first you don't succeed, then skydiving isn't for you. ~ Taglines by Taglinator - www.srtware.com ~ Steve Yates Integrated Technical Solutions, Inc. E-Mail - [EMAIL PROTECTED] Web- www.teamITS.com Phone - 630.420.2550 Fax- 630.420.2771 - 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
question on optimizing complex query
Hello, I am relatively new to MySQL though I have database experience. I have a query that doesn't seem to want to use an index for the first table despite my indexing several fields. Before I get too far in the details of the query, here is what EXPLAIN SELECT tells me for the first table of the LEFT JOIN: type=ALL possible_keys=NULL extra=where used; using temporary; using filesort I found a reference that usage of a temporary table ...typically happens if you do an ORDER BY on a different column set than you did a GROUP BY on. This is the case as my ORDER BY uses two columns while I group on one. Does that preclude the use of an index? Also, my first table has about 65 rows, and about 95% are used for the query (as limited by a WHERE condition), though any number of rows will be returned. Given that is the case, would that prevent MySQL from using an index? Or does it even matter with a table this size? For the record, it's the second table that has several thousand rows, and MySQL uses an index for that table in this query. - Steve Yates - if (stone != rolling) moss++; ~ Taglines by Taglinator - www.srtware.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
