StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSpeed up php with mysql query (joins vs creating a table at an interval)
text
Body
copied!<p>I have a website that has about 50,000 people in a database. To produce my statistics I need to pull data from 3 tables and join them together. More data comes in daily so the page is getting slower and slower. I was wondering if it would be possible to create a table that joins all the data I need and then run my php off that table. It would be great if the table creation could run hourly or some other set internal so that new data is included. Is this possible and advisable? Can you point me to some resources? </p> <p>I am using mysql for the database.</p> <p>Thanks!</p> <p>I have 3 tables here - the village level, the resident level, and if they are absent, an absent table with their results.</p> <pre><code> SELECT EU, sum(TF) as TFsum, sum(TT) as TTsum, sum(KID) as Nkid, sum(ADULT) as Nadult from (select EU, b.name as Person, CASE WHEN b.RIGHT_EYE_TF=1 THEN 1 WHEN b.LEFT_EYE_TF=1 THEN 1 WHEN c.RIGHT_EYE_TF=1 THEN 1 WHEN c.LEFT_EYE_TF=1 THEN 1 ELSE 0 END AS TF, CASE WHEN b.RIGHT_EYE_TT=1 THEN 1 WHEN b.LEFT_EYE_TT=1 THEN 1 WHEN c.RIGHT_EYE_TT=1 THEN 1 WHEN c.LEFT_EYE_TT=1 THEN 1 ELSE 0 END AS TT, CASE WHEN AGE <= 9 THEN 1 ELSE 0 END AS KID, CASE WHEN AGE >= 15 THEN 1 ELSE 0 END AS ADULT from villagedb a LEFT JOIN residentdb b ON a.CLUSTER = b.RES_CLUSTER LEFT JOIN absentdb c on b.RES_HOUSEHOLD_ID=c.RES_HOUSEHOLD_ID AND b.NAME = c.NAME GROUP BY EU, b.name ) S GROUP BY EU </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload