[PHP-DB] MySQL strangeness with joins and keys.
Hi all, This might be a bit OT but i'll ask anyway. I'm currently working on a project that involves using a database that contains about 1.8 million rows and am currently in the process of trying to get it to search as quick as posable. I've been using the explain keyword to try to see how mysql is handling the query and i am seeing some strange things. The query that i am currently using is: explain select RECORDING.RNO as rno, TRACK.TRA as track, ARTIST.ART as artist, RECORDING.REC as title, RECORDING.MUSIC as style, MEDIA.MEDIUM as medium from TRACK left join ARTIST use index (ano_art_index) on TRACK.ANO=ARTIST.ANO left join RECORDING use index (rno_rec_music_index) on TRACK.RNO=RECORDING.RNO left join MEDIA use index (rno_lno_medium_index) on RECORDING.RNO=MEDIA.RNO where TRACK.TRA like '%war pigs%' limit 0,5; which returns the following in the mysql client: +---+---+--... | table | type | possible_keys +---+---+--... | TRACK | index | NULL | ARTIST| ref | ano_index,ano_art_index | RECORDING | ref | rno_index,rno_rec_music_index | MEDIA | ref | rno_index,rno_lno_index,rno_lno_medium_index +---+---+--... ...+--+-+---+-... | key | key_len | ref | rows ...+--+-+---+-... | tra_rno_ano_index| 68 | NULL | 1775059 | ano_art_index| 4 | TRACK.ANO |1500 | rno_rec_music_index | 4 | TRACK.RNO |3458 | rno_lno_medium_index | 5 | RECORDING.RNO |5225 ...+--+-+---+-... ...+-+ | Extra | ...+-+ | where used; Using index | | Using index | | Using index | | Using index | ...+-+ Now what i don't understand is why Mysql is saying that there is no (NULL) keys available for table TRACK yet it does use the key 'tra_rno_ano_index' and it only looks in the index for the results (Using index). Also when searching using the query above it will take about 3 min to perform the search but if i change the line TRACK.TRA like '%war pigs%' to TRACK.TRA like '%what if%' the search will take anywhere from 0.5 - 6 seconds. Anybody have any idea why this is happening and how i can get a constant, and hopefully quick, speed across a number of different searches? If its any help im currently running MySQL 3.23.31 for freeBSD 4.2 Thanks, William. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] faq
[PHP-DB] installing
Hello, Got a major problem with seeing the result of my php scripts without uploading them to a server. I don't have a server myself. Do I need to have one or can I see my writen scripts and see the results directly in my internet explorer? thanks, ufo
Re: [PHP-DB] passing object elements in urls
On Tue, Feb 06, 2001 at 05:46:25PM +, Darryl Friesen wrote: Got a slight problem. I'm attempting to pass a number of variables from one form to another. The problem occurs when one of the object elements contains more than one word. [snip] Any idea how to pass object elements with spaces in a URL? Try encoding the spaces as %20 (the hex value for a space). There's probably a PHP function to do this for you (and one to do the reverse) -- I haven't looked. Yes there are: urlencode() and urldecode() http://www.php.net/manual/en/function.urlencode.php http://www.php.net/manual/en/function.urldecode.php Paul You could try using a + instead of a space too. - Darryl -- Darryl Friesen, B.Sc., Programmer/Analyst[EMAIL PROTECTED] Education Research Technology Services, http://gollum.usask.ca/ Department of Computing Services, University of Saskatchewan -- "Go not to the Elves for counsel, for they will say both no and yes" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- - Paul Cooper | Tel: 0121 331 7858 Senior Programmer and Database Engineer | Fax: 0121 687 7859 UCEcom | mailto:[EMAIL PROTECTED] University of Central England | http://www.ucecom.com Birmingham, B4 7DX | - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] installing
You will also need to have Personal Web Server installed on your workstation. Internet Explorer is not setup to run php by itself. It needs a webserver to do it. If you don't want to use Micrsofts Personal Web Server, you can download the win32 version of Apache at www.apache.org. I wouldn't bother with PWS. Apache or Xitami (www.xitami.com) are far easier to setup. I've installed PHP under both, with no problems. - Darryl -- Darryl Friesen, B.Sc., Programmer/Analyst[EMAIL PROTECTED] Education Research Technology Services, http://gollum.usask.ca/ Department of Computing Services, University of Saskatchewan -- "Go not to the Elves for counsel, for they will say both no and yes" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] installing php under apache
How do I installe the zip-module op php4 under the apache windows module? thank you, ufo
RE: [PHP-DB] PHP, MySQL and XML
http://freshmeat.net/projects/exist/ -Original Message- From: Thor M. Steindorsson [mailto:[EMAIL PROTECTED]] Sent: Monday, February 05, 2001 5:57 PM To: php-general; php-db Subject: [PHP-DB] PHP, MySQL and XML Does anyone have a solution for importing data from a RDF-XML file into a MySQL database? I've been racking my brain, as well as looking at tutorials and some xml parsers, but if someone has something like this it would save me tons of time. Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] redirection based on login username
*PHP is supported but there is no mySQL access on this host [not sure what the point is... maybe I should just tell them to switch hosts...] I am looking to see if i can connect to a freeservers sql DB [www.f2s.com] *It will be for agents who are logging in, depending on their assigned username they should be directed to THEIR pricing schedule, currently there are three different price schemes *I would like it to be expandable from the current [a new company, they are looking to grow fairly large... naturally] MY THOUGHTS *I only know how to do DB based authentication, so the agent may have to login TWICE [one for PHP redirect, another for apache protected dir. *Cookies might be the best way to go, (agent logs in to a gateway page that reads the cookie value and directs to according page) I don't like this cuz where does the cookie initially get set [give them each a disk with THEIR cookie on it?] *agent ID's could be assigned like this AGENT001 AGENT101 AGENT202 Then the script could direct to up to [10 price schedules, by using AGENT0, AGENT1, AGENT2, get it?] or [100 price schedules, if you extended to the second number AGENT01, AGENT02, AGENT10, AGENT11] etc. each could theoretically have unlimited agents. Just keep adding numbers after the "base" ID So, anyone have any ideas to go about this, I don't think that I can use a DB, unless maybe it was a flat text file... [never worked with those before:)] Thanks Much olinux _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Something Stupid
I have got to be doien something stupid. consider the following code. hosts, usernames and passwords have been changed to protect the innocent. ? $mysql_link = mysql_connect("myhost.domain.com","some.user","some.password " $myquery = "select car_no,drv_name from cars order by car_no + 0"; $myresult = mysql_db_query("topten", $myquery, $mysql_link); while($rows = mysql_fetch_array($myresult)); { print("$rows[0]br\n"); } ? I get no output and no errors. I use code almost identical to this all over the place, yet this one doesn;t work. I've tried just a bare qery and I still get nothing. the connection is working as we can see from the log entry below. At this point, I'm lost. If the technique I'm using is considered bad practice, I'm open to suggestions. My code would normally print out a table, but for the sake of trouble shooting, I reduced it to this which should print a single column of numbers. Anyone have any ideas? the mysqld.log records the query. 010206 22:59:55 310 Connect[EMAIL PROTECTED] on 310 Init DBtopten 310 Query select car_no, drv_name from cars order by car_no + 0 310 Quit
Re: [PHP-DB] Something Stupid
$mysql_link = mysql_connect("myhost.domain.com","some.user","some.password " you are missing a ); while($rows = mysql_fetch_array($myresult)); there is a ; that is not needed -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] installing
Hey I would say go with Linux and Apache with PHP 4.0 and MySQL. The best combination...Ive been using it for a while and its great even tho im just a newbie From: "Darryl Friesen" [EMAIL PROTECTED] To: "php maillijst" [EMAIL PROTECTED] Subject: Re: [PHP-DB] installing Date: Tue, 6 Feb 2001 13:49:51 -0600 You will also need to have Personal Web Server installed on your workstation. Internet Explorer is not setup to run php by itself. It needs a webserver to do it. If you don't want to use Micrsofts Personal Web Server, you can download the win32 version of Apache at www.apache.org. I wouldn't bother with PWS. Apache or Xitami (www.xitami.com) are far easier to setup. I've installed PHP under both, with no problems. - Darryl -- Darryl Friesen, B.Sc., Programmer/Analyst[EMAIL PROTECTED] Education Research Technology Services, http://gollum.usask.ca/ Department of Computing Services, University of Saskatchewan -- "Go not to the Elves for counsel, for they will say both no and yes" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]