How to fetch large data from database in mysql
Using MySQL to Handle Large Amounts of Data
Timecodes:
Hello and Welcome to using MySQL
to build Big Data applications
to build Big Data applications
This is going to be a tutorial about
obviously, using MySQL
obviously, using MySQL
to build Big Data applications, but
when I mean Big Data
when I mean Big Data
there could be two things, it could be..
there could be two things, it could be..
Sorry, there could be two problems that you are addressing. Either it's an problem of scaling
as in, my system already has a lot of data and I..
as in, my system already has a lot of data and I..
I would like to be able to
I would like to be able to
make the existing features more performant or
be allowed to get more volume
be allowed to get more volume
and the other problem is reporting
and the other problem is reporting
in the sense that you already have Big Data
in the sense that you already have Big Data
in the sense that you already have Big Data
and you are asked to make use of that in some way
and you are asked to make use of that in some way
and you are asked to make use of that in some way
to either give more insight
to either give more insight
to the business users in your organization or give aggregated reports
to your customers about how they are performing
to your customers about how they are performing
and I'm going to focus today, on this side (reporting)
and I'm going to focus today, on this side (reporting)
and I'm going to focus today, on this side (reporting)
of the Big Data the problem.
So what is the problem with with the Big Data?
So what is the problem with with the Big Data?
Basically, it's as if you have a very large table
Basically, it's as if you have a very large table
Basically, it's as if you have a very large table
with millions or billions of rows
with millions or billions of rows
and in order to do the reporting that you need to do
and in order to do the reporting that you need to do
you need to gather all this information from this table and process it in some way
you need to gather all this information from this table and process it in some way
However, what does that mean in terms of the underlying physics of it.
However, what does that mean in terms of the underlying physics of it.
However, what does that mean in terms of the underlying physics of it.
You have a hard disk
You have a hard disk
(let's pretend that's a hard disk)
and in order to get the certain rows from the table on the hard disk
and in order to get the certain rows from the table on the hard disk
you have to go over many different places in the hard disk
So, if it is a large amount of data (that) would obviously be more time consuming.
If the data is fragmented across different places on the hard disk that would mean you have to spin more.
If the data is fragmented across different places on the hard disk that would mean you have to spin more.
If the data is fragmented across different places on the hard disk that would mean you have to spin more.
and once you have that
you need to get that data into the CPU (roughly)
you need to get that data into the CPU (roughly)
you need to get that data into the CPU (roughly)
to aggregate that (data)
to aggregate that (data)
To process it (the data). To manipulate it into whatever way you need it to (be)
and then you produce a report
which you later provide to your users
which you later provide to your users
provide to your users
and they are happy about it
(I'm not sure if you can see that)
So, this problem has actually been going on for a very long time
So, this problem has actually been going on for a very long time
How are we able to, with existing hardware technologies,
How are we able to, with existing hardware technologies,
get more data faster to be able to process it and turn it into a report
get more data faster to be able to process it and turn it into a report
Many years ago, a person called Ralph Kimbal
who is the main or one of the two main contributors to the data warehousing
who is the main or one of the two main contributors to the data warehousing
who is the main or one of the two main contributors to the data warehousing
who is the main or one of the two main contributors to the data warehousing
he came up with.. data warehousing.. I wouldn't say movement, but technology
he came up with.. data warehousing.. I wouldn't say movement, but technology
he came up with.. data warehousing.. I wouldn't say movement, but technology
came up with the idea in 1995 or 1996
came up with the idea in 1995 or 1996
where he said basically, no matter what the technology is
where he said basically, no matter what the technology is
is we'll always have to go through a large number of rows
is we'll always have to go through a large number of rows
so how can we design our database
(in a way) that we are able to produce reports without
(in a way) that we are able to produce reports without
(in a way) that we are able to produce reports without
(in a way) that we are able to produce reports without
very resource intensive operations and
what he thought was
his solution to this program was basically to create something called a summary table
his solution to this program was basically to create something called a summary table
and a summary table is an aggregated
version of this table
obviously, smaller and with less rows
that data is already been
taken from here (the large table) and
summarized here (the small table). So when you access this summary table
it's obviously much easier to get the rows and much easier to give back the results
it's obviously much easier to get the rows and much easier to give back the results
it's obviously much easier to get the rows and much easier to give back the results
So let me give some examples about what
what that would look like
so let's say, you have
so let's say, you have
a table
and it has orders
like a basic e-commerce site
and you have
usually a hundred thousand rows
usually a hundred thousand rows
per day
so it's not really a
not really an issue for any relational database.
You store those rows
You store those rows
with the database. That's fine.
But your period of time, lets say a year
But your period of time, lets say a year
you have quite a large number of rows
So you start to have 36.5 million rows
and that could get cumbersome
and in some cases
it could be much more than 100,000 rows, but lets stick to this example
So you want to
create a report from the orders table and you want to know
create a report from the orders table and you want to know
The business users in your organization want to know how certain products doing across particular dates
The business users in your organization want to know how certain products doing across particular dates
The business users in your organization want to know how certain products doing across particular dates
What you could you do (is), you could create a summary table
What you could you do (is), you could create a summary table
like this
and
For the sake of clarity, I'll write a SELECT statement here that will explain the contents of the summary table. So you have
For the sake of clarity, I'll write a SELECT statement here that will explain the contents of the summary table. So you have
select
So lets say we need date, because that was what was requested
So lets say we need date, because that was what was requested
and any product_id
and we want to get the aggregated details of revenue
and we want to get the aggregated details of revenue
and we want to get the aggregated details of revenue
and then we GROUP BY it
date
Basically the two keys (columns)
date and product_id
This is now the new summary table and we can call it
product revenue summary
product revenue summary
product revenue summary
and this had to say we have . hundred products, so this will have
hundred rows a day
So obviously, you could
after generating this table
You could provide this table to your business users and say
"Do whatever you need. Find out whatever information you want to gather."
so lets say for example,
If someone were to query for product 13A
If someone were to query for product 13A
If someone were to query for product 13A
and how it did (performed) on weekends
and how it did (performed) on weekends
so perhaps you know you would find the table
so perhaps you know you would find the table
for weekends or dates
Get only weekends and perhaps INNER JOIN it with that (summary) table
Get only weekends and perhaps INNER JOIN it with that (summary) table
and you'll get their answer very quickly
and you'll get their answer very quickly
and you'll get their answer very quickly
and your users will be happy because of it
and your users will be happy because of it
A different sample
or a different summary table could be for people who are interested to know how the
product is selling across a particular geography
and in this case, lets say city
so
what we would need to do for that it's a city_id isn't recorded in the orders table
we would need to enrich
we would need to enrich
the table a little bit
and the way we do that is we
we INNER JOIN it with the addresses table
and
what we would do is we would, basically.. I'll just write it here
what we would do is we would, basically.. I'll just write it here
you would do SELECT
let's do
let's do
o for orders, o.date
and
city
and
sum(o.revenue) FROM orders o
INNER JOIN addresses a
INNER JOIN addresses a
on
on
(actually) using
address_id
address_id
GROUP BY date and city
and we will fill up a new summary table
and we will fill up a new summary table
called
called
city revenue summary
so here we have
two summary tables
Two different ways of slicing the data. Now
you aren't exactly limited by the number of summary tables you can have
you aren't exactly limited by the number of summary tables you can have
obviously, they take a certain amount of space and
obviously, they take a certain amount of space and
they also take some effort into creating (them), but we'll get into that soon
they also take some effort into creating (them), but we'll get into that soon
was you could have done for example here is that you could have added city to to product
so you have product
you have here date, product_id and city_id
make it a larger summary table, but you can get the data in two different ways
or perhaps you can then have a more extensive
more extensive summary table with a higher level of granularity
more extensive summary table with a higher level of granularity
You could search for product and city and date
that could be a user requirement. It depends.
if you're interested in getting to the data in one way
You are only interested in slicing the data in this way or slicing the data in this way (second summary table)
You are only interested in slicing the data in this way or slicing the data in this way (second summary table)
currently you have two summary tables
and this particular summary table has saved you an INNER JOIN
that could be quite valuable in terms of performance, saving you an INNER JOIN
So, those are the two examples
i'd just like to quickly
give another example
of what happens nowadays in some other companies
of what happens nowadays in some other companies
some social networks
Already kind of use the idea of summary tables
in their systems
lets say
they have lots of servers
it's spread geographically: this is Europe
This is North America. This is South America
and this is Asia
and this is Asia
and
in order for them to get reports that they are interested in
what they would do is they would get data
what they would do is they would get data
From all the servers
into lets say a map/reduce system
in this case lets say hadoop, for example
and remember, we don't need the exact
and remember, we don't need the exact
once it arrives here, we don't need the exact data from them. We need the aggregated data to goto
once it arrives here, we don't need the exact data from them. We need the aggregated data to goto
once it arrives here, we don't need the exact data from them. We need the aggregated data to goto
to another database or another summary table
to another database or another summary table
and once the data from here is aggregated
it goes into a reporting database
depending on their needs this can be mysql database
depending on their needs this can be mysql database
if their needs are greater, it could be
if their needs are greater, it could be
if their needs are greater, it could be
any number of commercial or open source solutions which can handle
larger amounts of data
But the theory is very similar to
the example of summary tables there was that you get
data from from someplace you
you summer you advocated in the clinton reporting databases manual use those
you know years those
uh... access to state the base
and
korea
according hollers as they see cannot from becky affirmative also creates
ripples on your own
but he's a study group or to chat
uh...
though you can't
change of course you can change according to the report is is as it is
whereas here
uh... if they want to change
uh... the query okay
different information once today
discover there's something wrong with that they prevail and it's not slacking
more information according to what they found that they can
uh... graders that was whereas here it's static
um...
so
as well
a lot of things you're adding basically have the leading ill to yield a delay
system a subsystem
uh... or something
and you need to know
basically
creator
he needs to
uh... make sure that
dates are rising too
data constantly
rising to the summary tables
and needs more thought everything goals according as well
soul
uh...
the joys residue of the owners
animal duets with
pass that you can do is the reason the some
is that the summertime blues yeah
uh... one is real time
and one is attached
university either on angel home
overlooking once an hour
or job it's that time
geranium for example when no one's yours it's over and
uh...
it's very securities the pros
lettuce for example it sorry
that seems a little ones once an hour soldier getting older all the all those
that happened
over the last hour and breaking it
i'm putting it into the summary tables
insect time
same principle but
for all the day's worth of episodes
it's becoming a bit
uh... large amount of
in these cases it's important to note that
the summit that was our only refreshed
you know once in a row once a day so
it's a business decision that's okay that's
and you can go out and do that
uh...
mcconnell's
martin duckworth
and
forestry with uh...
so it would be with
not saw this with his uh... and the system
you need to set up monitoring
because you can't just take for granted that you know that has a right you can
just create something like the select statement that i created
put in the crimes of and and all that nothing goes around you have to make
sure that that
at everything
is there's no warning messages no analysts is that the didgeridoo
as it should
um...
regarding my sql
to set something like this out
you can basically is a select statement that i i did
you know what the celts and uh...
well close depending on your
on norinko
an insult into
three-fifths if it's owns themselves for example you have uh...
once all alert reporting so lands
uh... you have a date the coming through petition which is the the riddles atm
so you can set up
magistrates mountain
for example
this is you're going to lose
and here is your role
mainstream
lazy reporting seem
annual to st louis yeah
and have for example you have the to some of them
and you would do use the insult statement here
and something to say pencils
uh... take it from here
things that the clinton
these two well
so or that openssl
insert it into
tabled
that the other
uh...
the other way of doing this is
perhaps a bit
bids moral
slightly more confusing but sometimes it's a requirement by
something databases
that you do select
uh... unsolved problem
some kind of fun
and then some kind of uh...
into our
on the twenty on august the loads
data
info on
command mysql
and
this is
sunday just helps with rick occasional absolute soaking
requirement so that was one of the please do whatever is more convenient
for you
i would say it
i would advocate
here for example e
wearing them as a group of
i would also bergmann ending a little boy now
because my skill by default
would have the uh... grew brian
also
w invisible although by columns that you chose and that means you have to do in
addition fossil thing
if you don't need that they don't require it
uh... estimate total
you can handle it by now and a commandment
uh... you can
so you can not
frank usual unique he's on on these
and you can
replacing terror that smoking principally no ring true
uh... you can
aren't story differs empl
murdered a job
between going to love the data uh...
uh...
it's only need a tony date arrives and that's fine but if for example
uh...
there's a
chance of although they could being updated then you have to
perhaps include more than when i walk for beta in into your interval you have
to maybe
diesel too little as i said replace interested in going to
ordinator that may be updated you have to either recalled which totals of days
ago you can just
bulk say
the last three six seven days
uh...
if any that there was a bit in that period of time please update
countries object sometimes with a vaca
submit something to look out for with uh...
what do you can do is best for do
uh...
solar duke however didn't go
that their cargoes quite good recently
and
this is still the same back
still blanche
i would say though that the relation that was regarding you know
the crew buys
this congregation tradition there is still very very strong
uh... you would
look at uh... wanted to do
even though it's questions to just one
beta versions not paralyzed very well
uh... its
if it takes a very long time you may want to a group in some way
uh... just the differences here that this is one senator can be you know five
six seven eight cells
uh... judge richard data so he would you would get it
heating digital mysql
database
interview with you
cluster
and i go back and kids
or you can have back employees have hurt
specific reporting data
regarding real time
interest in this basically means triggers
powell purification
and understands dirt itself so
daggers once you update uh... regulator base
when your insults and also that the riddle database
uh... yesterday the summary tables as well
in the same in the same uh...
instance
so
or is it is a bit of an overhead
uh... if you have a high note in the day the races
in general and uh... uh...
and you can take this additional rental grand
you may want to consider branch all you know i consider all
i don't want to use it
requirement has to be real time and i i think you're in for social networks this
is going to be like every requirement
um...
so
you have
basically when you have been so sick man here
uh...
you would
if it's tickets for example
you have uh...
some integration then you would
so you know for this particular line
adv did them on document the distance
this one role
into this table and that is
attitude
if it's been updated
change it if it's the lead to remove it
there is also in a lot of other functions functions average main
maxim that
can be more complicated
uh... there is an envelope arrived
mobile from other websites
speaks about that
how to get used to go out to right uh...
transformed
if alum
it's not an issue to add these two girls though it is an issue tablets relief
and it's a bit more convenient
because mona you don't need to monitor it so much
index
uh... if you insult if you answer that
and something goes wrong and i wanted to make sense of it so were friends you fix
it done and uh...
if you're using a tribute to log something and then delete legend and
asynchronous and
slugs accurate time
then you may need to use the monitor
but uh...
trying to confuse you
uh...
we've done basically trios
and you can't find him halted set that up
uh...
and was being
so
just as a summarize
even as the gorgeous progress
if you want to speed up you'll
your reports
it is a good idea to have some examples
uh...
and you can use that reports of those
it's very common
in places a couple of days now
and
uh...
i'm showing you can help you
it does take a bit of addition design and i haven't spoken about it looks a
bit of an example where
we've removed animal join with pcp
ambitions on
something you have to look at it more and as a group of seven given
too much code example soul
uh... this is really interesting obviously understand what some of the
rules are
um...
exit thank you for watching my sister
uh... if you want to contacting
uh... this reminder to us
anderson in mind
website slash blog
we can find out more information to fit in the past
thank you very much
Related queries:
how to fetch large data from database
how to fetch data from database in mysql
how to fetch the data from mysql in php
is fetch available in mysql
can mysql handle big data
