int (ID) is enclosed in quotes when using WHERE

[patriksh]

$this->dao->where('t.i_thread_id', $thread_id');

results in:

WHERE t.i_thread_id = '22745'

$this->dao->where("t.i_thread_id = $thread_id");

results in:

WHERE t.i_thread_id = 22745

This hurts performance and doesn't allow indexes to function well. We should check if where argument is a number and remove quotes in that case.

[patriksh]

Running a few queries resulted in an overall 16% of query time decrease.
These are very small numbers tho, but on bigger sites, databases, and queries it could help.

Note that i_thread_id is indexed.

[dev-101]

There's also this optimization here: osclass/Osclass#2293
However, I couldn't rewrite model side alone.

[patriksh]

There's also this optimization here: osclass/Osclass#2293
However, I couldn't rewrite model side alone.

No changes have been made yet regarding that?

I think just the INDEX they mentioned would help a lot. I'm working on optimizing some plugins on a large site and just by adding some good indexes the speed of queries was increased significantly.

[dev-101]

Nope. But, optimized query is not exactly the same, so adding index alone isn't good enough IIRC.

[patriksh]

Nope. But, optimized query is not exactly the same, so adding index alone isn't good enough IIRC.

Agree, but until we start working with optimizing the DAO classes, index is very easy to add.

[dev-101]

Yes, that part is easy :)

[navjottomer]

@webmods-croatia It would be better to compare it against the allowed characters.
ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

BTW I tried to run both queries with EXPLAIN and I didn't found any performance-related issue due to quotes nor I find any documentation related to performance impact.

EXPLAIN Select * from oc_t_item where fk_i_category_id = '31'

|id |select_type|table    |partitions|type|possible_keys   |key             |key_len|ref  |rows|filtered|Extra|
|---|-----------|---------|----------|----|----------------|----------------|-------|-----|----|--------|-----|
|1  |SIMPLE     |oc_t_item|NULL      |ref |fk_i_category_id|fk_i_category_id|4      |const|59  |100.00  |NULL |

EXPLAIN Select * from oc_t_item where fk_i_category_id = 31

|id |select_type|table    |partitions|type|possible_keys   |key             |key_len|ref  |rows|filtered|Extra|
|---|-----------|---------|----------|----|----------------|----------------|-------|-----|----|--------|-----|
|1  |SIMPLE     |oc_t_item|NULL      |ref |fk_i_category_id|fk_i_category_id|4      |const|59  |100.00  |NULL |

As you can see in above case both queries are using indexes.

[patriksh]

@navjottomer I ain't an expert with this, but that's what EverSQL told me. The performance difference is there.

[navjottomer]

Can you share the report? If the performance difference is there then we should see the whole example.

[navjottomer]

Closing this for now.