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;
}
#2

[eluser]TheFuzzy0ne[/eluser]
My first reaction is to suggest you don't order by RAND(). It actually re-orders the rows, which can be slow in itself. Instead, I'd suggest you count the rows, and do something like this:
Code:
$this->db->where('grade', $grade);
$row_count = $this->db->count_results('kg_compounds');

$this->db->where('grade', $grade);
$this->db->limit(1, rand(0, $row_count-1));
$res = $this->db->get('kg_compounds');

It's two queries, but I'd be interested to know how much of a difference it makes (if any).

Sorry, I know I've totally avoided your question, but I am about to dash off out the door, so I thought I should point that out.
#3

[eluser]Leon Stafford[/eluser]
[quote author="TheFuzzy0ne" date="1245266382"]My first reaction is to suggest you don't order by RAND(). It actually re-orders the rows, which can be slow in itself. Instead, I'd suggest you count the rows, and do something like this:
Code:
$this->db->where('grade', $grade);
$row_count = $this->db->count_results('kg_compounds');

$this->db->where('grade', $grade);
$this->db->limit(1, rand(0, $row_count-1));
$res = $this->db->get('kg_compounds');

It's two queries, but I'd be interested to know how much of a difference it makes (if any).

Sorry, I know I've totally avoided your question, but I am about to dash off out the door, so I thought I should point that out.[/quote]

Thanks again TheFuzzyOne!

I will implement something like that for the random part.

If you come back before anyone else replies, would be thrilled if you could check out the meaty part of the code, too :cheese:
#4

[eluser]jedd[/eluser]
Howdi,

I can see what you're doing, but I can't be sure that I know what you're trying to do. Can you throw together an example of input and what output you would expect from that. It looks like a really simple LEFT JOIN is required .. but immersing myself in other people's nomenclature also makes my brain melt .. so want to be sure it's for a good cause Wink

Like Fuzzy, I'll throw in a freebie to whet your appetite. Replace your first bit with this (yes, I know you're going to replace it all ... but for future reference)

Code:
function get_word_list($level = 1, $isShuffled = false)  {
    $user = $this->db_session->userdata('id');

    $qstring = "SELECT *
                FROM kg_compounds
                WHERE grade='$level'";

    if ($isShuffled)
        $qstring .= " ORDER BY RAND()";

    $query = $this->db->query($qstring);
#5

[eluser]Leon Stafford[/eluser]
[quote author="jedd" date="1245271320"]Howdi,

I can see what you're doing, but I can't be sure that I know what you're trying to do. Can you throw together an example of input and what output you would expect from that. It looks like a really simple LEFT JOIN is required .. but immersing myself in other people's nomenclature also makes my brain melt .. so want to be sure it's for a good cause Wink

Like Fuzzy, I'll throw in a freebie to whet your appetite. Replace your first bit with this (yes, I know you're going to replace it all ... but for future reference)

Code:
function get_word_list($level = 1, $isShuffled = false)  {
    $user = $this->db_session->userdata('id');

    $qstring = "SELECT *
                FROM kg_compounds
                WHERE grade='$level'";

    if ($isShuffled)
        $qstring .= " ORDER BY RAND()";

    $query = $this->db->query($qstring);
[/quote]

Thanks Jedd,

I think I should sacrifice my own brain, get off my butt and study SQL again Tongue

I will post back if JOIN won't work and include some IO data.

It's basically for a learning system where known words get marked against a user name in the kg_progress table.

Starting a quiz using this code, it grabs all the words for a level, then looks in the progress table to see if any of the words are in there, if they are, it leaves them out...
#6

[eluser]jedd[/eluser]
Okay .. rough draft absent further understanding is this:
Code:
SELECT *
    FROM kg_compounds
    LEFT JOIN kg_progress ON kg_progress.compound=kg_compounds.id
    WHERE grade='$level'
    AND kg_progress.compound_progress=1
UNTESTED, mind. Perhaps something for you to start playing with, at least.

Can I suggest that for foreign keys - for example your kg_progress.compound - that you call them the other table name with a _id suffix.

So rather than kg_progress.compound you'd call it kg_progress.compound_id

I'd also suggest that you try to avoid having column names that are the same as table names - it's just plain confusing, and hints at a badly considered / designed schema. Often renaming the column as 'name' or 'description' is sufficiently descriptive.
#7

[eluser]Leon Stafford[/eluser]
[quote author="jedd" date="1245273001"]Okay .. rough draft absent further understanding is this:
Code:
SELECT *
    FROM kg_compounds
    LEFT JOIN kg_progress ON kg_progress.compound=kg_compounds.id
    WHERE grade='$level'
    AND kg_progress.compound_progress=1
UNTESTED, mind. Perhaps something for you to start playing with, at least.

Can I suggest that for foreign keys - for example your kg_progress.compound - that you call them the other table name with a _id suffix.

So rather than kg_progress.compound you'd call it kg_progress.compound_id

I'd also suggest that you try to avoid having column names that are the same as table names - it's just plain confusing, and hints at a badly considered / designed schema. Often renaming the column as 'name' or 'description' is sufficiently descriptive.[/quote]

Thanks again Jedd,

There goes my sleep tonight, I will try and get this happening...

I totally get you with the bad naming conventions used.

That said, is it technically possible to do any kind of JOINs even if the tables aren't named well, ie using AS to cast it to a different name for the query instance?

I got errors on that join and fiddled a bit but haven't got it yet.

Also, I am trying to get all the rows, EXCLUDING the ones marked "1" in the kg_progress table, can I still use JOIN xxxxxx = 1 in that case or !=1 or do I need a different operand than JOIN?

I regret not having been more interested in MS Access in high school computer class Tongue

Cheers,

Leon
#8

[eluser]Leon Stafford[/eluser]
OK, not using JOIN, but this is working a treat!

Code:
function get_kanjisearch_list($level = 1, $isShuffled = false)
    {
        
        $user = $this->db_session->userdata('id');
      
        
        if ($isShuffled)
        {
            //
        }
        else
        {
          
            
            $query = $this->db->query("            
            SELECT *
            FROM kg_compounds
            WHERE NOT
            EXISTS (
                SELECT compound
                FROM kg_progress
                WHERE kg_progress.compound = kg_compounds.id AND
                (
                kg_progress.compound_progress =1 AND kg_progress.user = '$user'
                )
            
            )
            AND grade = '$level'
            
            LIMIT 20
            
            ");
            
            
        }
        
        
        $compound_list = array();

        foreach ($query->result() as $row)
        {
            
                            $compound_list[] = array(    'id' => $row->id,
                                                'compound' => $row->compound,
                                                'reading' => $row->reading,
                                                'meaning' => $row->meaning,
                                                'grade' => $row->grade,
                
                                            );    
                    
                
        }
        
        
        return $compound_list;
    
    }

Next, I will implement the better random() methods mentioned in the above posts Big Grin
#9

[eluser]jedd[/eluser]
Quote:That said, is it technically possible to do any kind of JOINs even if the tables aren't named well, ie using AS to cast it to a different name for the query instance?

Of course .. you can cast any of those fields using an AS. That's the easy bit.

Quote:I got errors on that join and fiddled a bit but haven't got it yet.
Errors ...? Go on .. share them. Smile I don't have a set of tables with a suitably/ speculatively comparable set of data to test my calls against, so I've just dodgied something up. If you have a couple of short INSERT scripts, that'd be handy.

Quote:Also, I am trying to get all the rows, EXCLUDING the ones marked "1" in the kg_progress table, can I still use JOIN xxxxxx = 1 in that case or !=1 or do I need a different operand than JOIN?

Of course. Here's one of my nonsensical ones I was playing with earlier.
Code:
SELECT *
    FROM organism
    LEFT JOIN taxa_species ON taxa_species.id!=organism.taxa_species
    WHERE taxa_species.id=1
    AND organism.id=1;

Quote:I regret not having been more interested in MS Access in high school computer class Tongue

Your disregard of MS Access serves only to improve others' regard of you, I'm sure.
#10

[eluser]Leon Stafford[/eluser]
[quote author="jedd" date="1245278442"]Errors ...? Go on .. share them. Smile [/quote]

Hi jedd, thanks again.

The errors were from the JOIN ?? ON ?? line, basically the errors were as descriptive as me...

I used the ammendment for the shuffle here:

Code:
$query_string = "            
        SELECT *
        FROM kg_compounds
        WHERE NOT
        EXISTS (
            SELECT compound
            FROM kg_progress
            WHERE kg_progress.compound = kg_compounds.id AND
            (
            kg_progress.compound_progress =1 AND kg_progress.user = '$user'
            )
        
        )
        AND grade = '$level'
        
        
        
        ";

        if ($isShuffled)
        {
            $query_string .= " ORDER BY RAND()";
        }    
        $query_string .= " LIMIT 20";
            
        $query = $this->db->query($query_string);

I know I should implement the better random code as I've noticed that a lot on the forums.

And jedd, thanks for the LEFT JOIN sample, I think I should aim to migrate from the current solution to a JOIN for performance, right?




Theme © iAndrew 2016 - Forum software by © MyBB