No Support > Scheduled for cpg1.5.x

Optimize requests of CPG for next versions ? ...

<< < (2/12) > >>

nol33t:

--- Quote from: Tarique Sani on March 10, 2005, 07:27:52 am ---what really kills the MySQL is the NOT IN clause with long list of aids.... As of now I do not know how to avoid that.

--- End quote ---
candid (ok not that much :P) point of view: why then not doing it, and do the filtering on the script side? I had experiences where that happend to be the most efficient solution

Tarique Sani:
@nol33t - elaborate

Rodinou:
happy my request is not an invalid request ...

I'm afraid in fact because my site is known by a lot of people : this summer, we'll cover "Sziget Festival" and I'll have thousands new visitors for photos of the biggest european festival ... if my server CRASHES today ... I don't want to think about in few months ...

I'll wait your solution to use coppermine with fantasya (which has MORE visitors that my site sortons.net ...)

But I remember, when we dev' fantasya.net ... we have done a caching system about 5mn .... and it could support 20 000 visitors / day without problem.

nol33t:
@Tarique
I'm working on a project where some table rowcount went from 1 to 15 millions in one year

The same queries which were taking 2 min to run, suddenly ( really suddenly, i mean the next day ) were taking 45 min
Some of those queries were queries with nested NOT IN in it.
From what i learnt: the sql engine, depending on the size of the table, choose or not to use the index of the table when running subqueries.
When you reach a certain point ( i'm not dba so i won't go into more details ), the engine decides not to use the index anymore, but to do a table scan --> that's when you start to get in trouble.

To solve that and get back (lot) faster running queries, depending on the need, we did one of the two following things:

- replaced queries of the kind

--- Code: ---select * from a where a.id not in ( select id from b where etc...)

--- End code ---

with

--- Code: ---select b.id into #tmptable_b where etc.. ( syntax in mysql being "select into temporary table tmptable_b i think..)

select * from a where a.id not in ( select id from #temptable_b )

--- End code ---

OR  ( and i think that example fits more w/ cpg )

- replaced the same query

--- Code: ---select * from a where a.id not in ( ... ) and a.another_column = 'another_value"

--- End code ---
with

--- Code: ---select * from a where a.another_column = 'another_value'

--- End code ---

then in the script/program,
after fetching the results
(pseudo coding here )

--- Code: ---forbidden_set = list( 4, 7, 9, 12) //list recovered by sql also
if ( forbidden_set.contains( mysql_result[$i.['aid']] )
then
  skip //NOT IN replacement
else
  process it

--- End code ---

Those are just some thoughts
but even if the numbers are differents..i think the concept stays the same

-matt-

Tarique Sani:
@nol33t - you obviously are not too familiar with CPG  - go figure :P

Navigation

[0] Message Index

[#] Next page

[*] Previous page