with clause


Posted on 16th Feb 2014 07:03 pm by admin

hi all, i have a big query that usually access the same table more than once. for example, i have something like this

select
from table1 where id = 124
Did you know?Explore Trending and Topic pages for more stories like this.
union all
select
from table2 a, (select * from table1 where id 123) b
where a.id=b.id
union all
select
from table3 a, (select * from table1 where id = 345) b
and a.id=b.id

as you can see table1 is part of every union. the problem is that table1 is a huge table retrieving about 11 million records. accessing this table multiple time is hurting performance. i tried using With clause when i look a the explain plan the number of bytes is about 15 g

my question is how can i re-write the above query without accessing the table1 multiple times like i am in the code above.
if i use the with clause, the explain plan show big chuck of memory been use. thanks
No comments posted yet

Your Answer:

Login to answer
306 Like 47 Dislike
Previous forums Next forums
Other forums

filesize() returns 0
I have a socket program that runs in an infinite loop, listening for connections, in this socket I h

confused between ' ' and " "
there is a php i set:
$begin_date_query = mysql_query( "SELECT SUBDATE(due_date, INTERVAL $d

Create a form of 2 numbers input and find the greatest.
Hi, everybody.

I have a homework in my training of php, which ask you to make a form that ask

Advice on how to delete a mysql row using my form
Hi,

I am very new to php and am struggling to work out how to delete a portfolio item (a row

Newb advice
Hi all,

I'm a flash front end designer and I've taken on a project that needs some back end p

Query issues
I probably have this setup wrong well I know I do because nothing is showing up now. What I want is

Replacing a string on click of a button
Hi! Is there a way to replace a string on a click of a text link?

This is what I've come up w

Sort Alternative/bi-monthly
Hi all,

Am going mad trying to get something to work.

I'm creating a CMS for a magazi

Date/Time and Checkbox
Hi Everyone,

I am trying to figure out how to insert the current date and tim

How to disable direct access to a file
Suppose I've 2 Files. 1.php & 2.php

I don't want anybody to access 2.php directly fr

Sign up to write
Sign up now if you have flare of writing..
Login   |   Register
Follow Us
Indyaspeak @ Facebook Indyaspeak @ Twitter Indyaspeak @ Pinterest RSS



Play Free Quiz and Win Cash