@William, consider the classic example of customer-orders-order_details-products: customer: id | name | email orders id | order_number | date | customer_id order_details id | order_id | product_id | qty products id | name | description | unit_price With these constraints: 1. A customer can make many orders 2. An order can have many products Suppose then, you have users who need to see information in this format: customer name | customer email | product name | qty | unit price | total For EACH product ordered (i.e. for each row in order_details), how would you do it? In my scenario, the SELECT part fetches specific data from 3 different tables and lays it out in a format that the report expects, and I chose to create it as a permanent table instead of a view. Is this what you call duplicating table structure? Thanks for the heads up on cacti, I didn't know I could do that. But the point of contention is this: Suppose you have a table with 5 columns, and you need to select all of them; is SELECT * any worse than naming those 5 columns? On Thu, Oct 24, 2013 at 4:03 PM, William Muriithi < william.muriithi@gmail.com> wrote:
@Ndwex, nope, I am not duplicating table structure. I am following something I heard from some Facebook engineering video "store the data in the state its going to be used". The actual SELECT I use from my_table_b
is
a little bit more complicated. Like I mentioned, its a particularly nasty piece of SQL which takes 40 seconds to run, and does some calculations and transformations that honestly gives me headaches.
It may have been suggested by Facebook guy but you are being dishonest by saying its not table duplication.
And to me, sound like an over engineered nightmare. Sorry for the people who will support the system and devs who will maintain the code.
And then you raise something interesting... A colleague once challenged me to practically and factually show him why *SELECT column_a, column_b, column_c FROM some_table *is better than *SELECT ** *FROM some_table*. EXPLAIN
and EXPLAIN EXTENDED didnt show me any difference between the two.
Would be petty easy demonstration. Set up a MySQL database with significant data and graph I/O through cacti. Then set up a clone to run some random select * . After collecting enough I/O data, turn the select * to select column x and compare the 2 I/O graphs.
Seriously, this don't even need that demonstration. Its petty logical to assume pulling out a full table from hard disk is more involving that pulling a column or two.
Muriithi
On Thu, Oct 24, 2013 at 12:31 PM, Ken Muturi <muturiken@gmail.com>
wrote:
I think peter is trying to have data from one table to another table
but
in a different format. Perhaps the one table is a Key => value but now wants to create a linear/structured table with some of key/values. some kind of PIVOTING. Mysql does it by MAX CASING. (I have been there.) .
@Nd'wex => a view may not be appropriate probably because of performance (in a view u will just be running the same query again and again from the parent.) which i suppose peter is why he is creating the next table because of performance issues.
On Thu, Oct 24, 2013 at 12:22 PM, Nd'wex Common <flexycat@gmail.com wrote:
@peter looking at the query seems like you are duplicating a tables structure and copying the respective record which in turn brings up the arguments by nelson (above) why not create a view?
tip: select * is more hungry resource wise that select id
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------