Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It's important to know that doing a <code>LIKE 'XXXX%'</code> is very slow if it's not indexed. This can take forever in some cases. Also, you're doing four sub selects rather than joins which is again slower. Below I've added a full query which will produce the same results. The alternative to this is to reduce the size of the query by reducing the number of joins and instead have a NORMALIZED output. That is for every <code>nric</code> and <code>name</code> you'll have multiple rows with instead of <code>re_mark</code> for every type of class (english, maths, 2008, 2009, etc) you'll have two columns which contain the code (FEEN or FEMA) and the mark.</p> <p>Give the following query a try and see if it works any better for you:</p> <pre><code>SELECT s.s_nric AS nric, s.s_name AS name, s.s_psle_eng AS psle_eng, s.s_psle_math AS psle_maths, s.s_psle_aggr AS psle_aggr, e_2008_feen.re_mark AS english_2008, e_2008_fema.re_mark AS maths_2008, e_2009_feen.re_mark AS english_2009, e_2009_fema.re_mark AS maths_2009, isc.isc_g_gpa AS isc_gpa FROM si_student_data AS s INNER JOIN si_results e_2008_feen ON e_2008_feen.re_code LIKE 'FEEN%' AND e_2008_feen.re_year = '2008' AND e_2008_feen.re_semester = '2' AND e_2008_feen.re_nric = s.s_nric INNER JOIN si_results e_2008_fema ON e_2008_fema.re_code LIKE 'FEMA%' AND e_2008_fema.re_year = '2008' AND e_2008_fema.re_semester = '2' AND e_2008_fema.re_nric = s.s_nric INNER JOIN si_results e_2009_feen ON e_2009_feen.re_code LIKE 'FEEN%' AND e_2009_feen.re_year = '2009' AND e_2009_feen.re_semester = '2' AND e_2009_feen.re_nric = s.s_nric INNER JOIN si_results e_2009_fema ON e_2009_fema.re_code LIKE 'FEMA%' AND e_2009_fema.re_year = '2009' AND e_2009_fema.re_semester = '2' AND e_2009_fema.re_nric = s.s_nric LEFT JOIN si_isc_gpa AS isc ON isc.isc_g_nric = s.s_nric WHERE s.s_admission_year = '2008' GROUP BY s.s_nric ORDER BY s.s_gender, s.s_name ASC </code></pre> <p>Edit: Included a normalized version:</p> <pre><code>SELECT s.s_nric AS nric, s.s_name AS name, s.s_psle_eng AS psle_eng, s.s_psle_math AS psle_maths, s.s_psle_aggr AS psle_aggr, si_results.re_code AS code si_results.re_mark AS mark si_results.re_year AS year isc.isc_g_gpa AS isc_gpa FROM si_student_data AS s INNER JOIN si_results e_2008_feen ON si_results.re_nric = s.s_nric LEFT JOIN si_isc_gpa AS isc ON isc.isc_g_nric = s.s_nric WHERE s.s_admission_year = '2008' AND si_results.re_year in ('2008', '2009') AND si_results.re_semester = '2' AND ( si_results.re_code LIKE 'FEEN%' OR si_results.re_code LIKE 'FEMA%' ) GROUP BY s.s_nric ORDER BY s.s_gender, s.s_name ASC </code></pre> <p>This will produce rows like:</p> <pre><code>nric: 1 name: 'student 1' psle_eng: eng1 psle_maths: maths1 psle_aggr: aggr1 code: FEENXXXX mark: 5 year: 2008 isc_gpa: gpa1 nric: 1 name: 'student 1' psle_eng: eng1 psle_maths: maths1 psle_aggr: aggr1 code: FEENXXXX mark: 3 year: 2009 isc_gpa: gpa1 nric: 1 name: 'student 1' psle_eng: eng1 psle_maths: maths1 psle_aggr: aggr1 code: FEMAXXXX mark: 4.5 year: 2008 isc_gpa: gpa1 nric: 1 name: 'student 1' psle_eng: eng1 psle_maths: maths1 psle_aggr: aggr1 code: FEMAXXXX mark: 5 year: 2009 isc_gpa: gpa1 </code></pre> <p>Notice that the only values changing for the four records is <code>code</code>, <code>mark</code>, and <code>year</code>. You'll get 4 times the number of records back as before but it should run significantly faster than before as well. Your code will have to iterate all the rows and aggregate them as needed.</p>
    singulars
    1. This table or related slice is empty.
    1. POplease check my MYSQL query & give me advice?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMatt S
    1. USMatt S
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi , thanks. am trying now. wait please
      singulars
      1. PO
      1. USSuba
    2. COI appreciate your help.
      singulars
      1. PO
      1. USSuba
    3. CONAME IC ENGLISH_2008 ENGLISH_2009 MATHS_2008 MATHS_2009 ISC_GPA <br> xxx 1 20 40 50 55 2.25 I need results like this. I like your query. very fast. pls advise me
      singulars
      1. PO
      1. USSuba
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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