*solved* Re: [ACFUG Discuss] Re: Speeding up execution time against 2.3 m records

Fri, 08 Sep 2006 09:27:23 -0700

The final solution was/is much simpler. However, there is still a query speed advantage with PHP. A colleague ran the same queries, on the same model machine, using the same MySQL DB, and MySQL version, only difference was PHP and the query times were much, much faster. How much? Well, after optimizing (as indicated below), my query times are down to 0.0003 (3 MS) on average. Not bad at all. But the same query on PHP (again, PHP being the ONLY change) was taking less than 1 MS on average, usually about 0.00005 0.5MS).  You can see the CF page here:

The trick/key was to change the default index that came with the DB (data and schema). It was a PK index, clustered on ipTo and ipFrom, both INT (10) fields. That has been deleted and now, the index is simply on ipFrom. No other index, no PK. 

Then, instead of searching for a value on each side/end of the IPv4 number (a range), the query is now: 

SELCT * FROM table WHERE ipFrom <= inet_aton('64.105.194.201') ORDER BY ipFrom DESC LIMIT 1


The limit tells MySQL to stop looking essentially. The order by forces us to get the right record. So, problem solved. And, as a test, i did 35,000 of these look ups, along with 35,000 DB updates to a second table in just under 33 seconds. Not bad at all.


And while the query was faster in PHP, the actual output, the display of the page via CF/Apache and in the users browser, is much faster in CF than in PHP (in this case). 


_____________ 

Derrick Peavy

Sales and Web Services 

Universal Advertising

Phone: 404-786-5036

Fax: 404-370-0470 

http://www.universaladvertising.com 

http://www.collegeadvertising.com 

http://www.collegeclassifieds.com 

___________________________________


On Sep 7, 2006, at 10:08 PM, Cameron Childress wrote:

without seeing all the php and cf setup info and code I don't know.

maybe, maybe not

On 9/7/06, Steven Ross <[EMAIL PROTECTED]> wrote:
Would that still be the reason for the huge slowdown between php and
CF? I would think there would be something else there causing that...
just curious.

On 9/7/06, Cameron Childress <[EMAIL PROTECTED]> wrote:
> On 9/7/06, Cameron Childress <[EMAIL PROTECTED]> wrote:
> > Does this not accomplish the same thing?
>
> Heh - I accidentally deleted what I put here, but it was essentially a
> subselect.  A self join as Dean suggested also might work.
>
> The key is just to minimise the data MySQL and CF have to shuttle back
> and forth and keep any larger datasets inside the MySQL machine.
>
> -Cameron
>
>
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @
>
> List hosted by http://www.fusionlink.com
> -------------------------------------------------------------
>
>
>
>


--
Steven Ross
web application & interface developer
[mobile] 404-488-4364
[fax] 928-484-4364


-------------------------------------------------------------
To unsubscribe from this list, manage your profile @

-------------------------------------------------------------






-- 
Cameron Childress
Sumo Consulting Inc
---
cell:  678.637.5072
aim:   cameroncf


-------------------------------------------------------------
To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform

-------------------------------------------------------------





Reply via email to