Re: [PHP-DB] Slooooow query in MySQL.
select h.addr, h.city, h.county, h.state, h.zip, 'yes' as show_prop, h.askingprice, '' as year_built, h.rooms, h.baths, '' as apt, '' as lot, h.sqft, h.listdate, '' as date_sold, h.comments, h.mlsnum, r.agency, concat(r.fname, ' ', r.lname) as rname, r.phone as rphone, '' as remail, '' as status, '' as prop_type, ts.TSCNfile as picture, h.homeid as homeid, 'yes' as has_virt from ProductStatus ps, home h, realtor r, ProductBin pb left join TourScene ts on ts.TSCNtourId = pb.PBINid and ts.TSCN_MEDIAid = '3' where ps.PSTSstatus = 'posted' and pb.PBINid = PSTS_POid and h.id = pb.PBINid and h.listdate DATE_SUB(NOW(), INTERVAL 2 YEAR) and (h.homeid is not null and h.homeid '') and r.realtorid = pb.PBIN_HALOid limit {l1}, {l2} Here is the query. I didn't know that it needed to have an ORDER clause in it for the limit to work properly. I'll probably order by h.listdate -- Rob Stut [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Chris wrote: Stut wrote: Chris wrote: Rob Adams wrote: I have a query that I run using mysql that returns about 60,000 plus rows. It's been so large that I've just been testing it with a limit 0, 1 (ten thousand) on the query. That used to take about 10 minutes to run, including processing time in PHP which spits out xml from the query. I decided to chunk the query down into 1,000 row increments, and tried that. The script processed 10,000 rows in 23 seconds! I was amazed! But unfortunately it takes quite a bit longer than 6*23 to process the 60,000 rows that way (1,000 at a time). It takes almost 8 minutes. I can't figure out why it takes so long, or how to make it faster. The data for 60,000 rows is about 120mb, so I would prefer not to use a temporary table. Any other suggestions? This is probably more a db issue than a php issue, but I thought I'd try here first. Sounds like missing indexes or something. Use explain: http://dev.mysql.com/doc/refman/4.1/en/explain.html If that were the case I wouldn't expect limiting the number of rows returned to make a difference since the actual query is the same. Actually it can. I don't think mysql does this but postgresql does take the limit/offset clauses into account when generating a plan. http://www.postgresql.org/docs/current/static/sql-select.html#SQL-LIMIT Not really relevant to the problem though :P How many queries do you run with an order? But you're right, if there is no order by clause adding a limit probably will make a difference, but there must be an order by when you use limit to ensure the SQL engine doesn't give you the same rows in response to more than one of the queries. -Stut -- http://stut.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Slooooow query in MySQL.
I have a query that I run using mysql that returns about 60,000 plus rows. It's been so large that I've just been testing it with a limit 0, 1 (ten thousand) on the query. That used to take about 10 minutes to run, including processing time in PHP which spits out xml from the query. I decided to chunk the query down into 1,000 row increments, and tried that. The script processed 10,000 rows in 23 seconds! I was amazed! But unfortunately it takes quite a bit longer than 6*23 to process the 60,000 rows that way (1,000 at a time). It takes almost 8 minutes. I can't figure out why it takes so long, or how to make it faster. The data for 60,000 rows is about 120mb, so I would prefer not to use a temporary table. Any other suggestions? This is probably more a db issue than a php issue, but I thought I'd try here first. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: check boxes and php
The trick here is to use the right HTML field name and PHP syntax to receive the information. For your checkboxes in HTML, name them like this: input type=checkbox name=del[] value=1Favorite 1/input input type=checkbox name=del[] value=2Favorite 2/input Then, in your PHP code, first check to see if there is more than one value: if (is_array($_POST[del])) { // works only if id is all integers. $in_list = implode(, , $_POST[del]); $query = delete from table where id in ($in_list); } else $query = delete from table where id = {$_POST['del']}; That should work, no javascript necessary. -- Rob John Dillon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] m... I want to create a page where the user will have a list of its favorites and by ticking a box opposite the item will be able to delete it by clicking the submit button. I want to make this dynamic so that the php works out the one to delete based on the value of the tickbox (I'm thinking), having first - done a query to retrieve the favorites of the user - listed the favorites on the browser and set the tick boxes with a value corresponding to the ID of the favorite...thus knowing which one to delete. I was wondering what's the best approach - the one above or to mix in some javascript? Any examples out there? I know it's a popular idea. Maybe there's a better approach than tickboxes. John http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php