Welcome Guest, Not a member yet? Register   Sign In
Help tuning a very poor SQL query
#1

[eluser]Leon Stafford[/eluser]
Hi,

I have hacked together some code to do what I want, but it is totally inefficient and I would be so happy if someone could help me optimize it using JOINS or whatever works (my brain tends to melt with DB stuff...)

Code:
###################
//My db setup

+----------------------+
| my tables               |
+----------------------+
| kg_compounds         |
| kg_progress          |
+----------------------+

mysql> describe kg_progress;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user              | varchar(45)      | NO   |     |         |                |
| kanji             | varchar(6)       | NO   |     |         |                |
| progress          | tinyint(4)       | NO   |     | 0       |                |
| compound          | int(11)          | NO   |     |         |                |
| compound_progress | tinyint(4)       | NO   |     |         |                |
+-------------------+------------------+------+-----+---------+----------------+

mysql> describe kg_compounds;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| compound   | varchar(50)      | NO   |     |         |                |
| reading    | varchar(200)     | YES  |     | NULL    |                |
| meaning    | varchar(200)     | YES  |     | NULL    |                |
| grade      | varchar(30)      | YES  |     | NULL    |                |
| usefulness | tinyint(4)       | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+

The kg_progress.compound corresponds to kg_compounds.id

The kg_progress.compound_progress is set to 0 or 1

Here is the code which is WORKING, just very slowly and without allowing me to add a proper LIMIT...

Code:
function get_word_list($level = 1, $isShuffled = false)
{
    
    $user = $this->db_session->userdata('id');
            
    if ($isShuffled)
    {
        $query = $this->db->query("
        SELECT *
        FROM kg_compounds
        WHERE grade='$level'
        ORDER BY RAND()");
    }
    else
    {
        $query = $this->db->query("
        SELECT *
        FROM kg_compounds
        WHERE grade='$level'
        ");
    }
    
    $compound_list = array();

    foreach ($query->result() as $row)
    {
        
        $query2 = $this->db->query("
        SELECT * FROM kg_progress
        WHERE user     = '$user' AND
        compound     = '$row->id' AND
        compound_progress = 1
        ");            
        
        if ($query2->num_rows() < 1)
        {
                $compound_list[] = array(    
                                    'id'         => $row->id,
                                    'compound'     => $row->compound,
                                    'reading'     => $row->reading,
                                    'meaning'     => $row->meaning,
                                    'grade'     => $row->grade,
                                    );    
        }    
                
            
    }
    
    return $compound_list;
}


Messages In This Thread
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 08:03 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 08:19 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 08:24 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 09:42 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 09:47 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 10:10 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 10:28 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 11:21 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 11:40 AM
Help tuning a very poor SQL query - by El Forum - 06-17-2009, 11:49 AM
Help tuning a very poor SQL query - by El Forum - 06-18-2009, 05:49 AM
Help tuning a very poor SQL query - by El Forum - 06-18-2009, 05:53 AM
Help tuning a very poor SQL query - by El Forum - 06-18-2009, 06:02 AM
Help tuning a very poor SQL query - by El Forum - 06-18-2009, 09:57 AM
Help tuning a very poor SQL query - by El Forum - 06-18-2009, 10:14 AM



Theme © iAndrew 2016 - Forum software by © MyBB