Welcome Guest, Not a member yet? Register   Sign In
Ability to specify index for query build from
#1

Hi,
after migrating some tables to InnoDB I notice very slow queries when using COUNT().
The solution is to force an index to the DB, something that is not possible to do with CI currently (or maybe I haven't found a way). For the moment I am using hardcoded SQL queries, but this is not the way to go I think.

Am I missing something or do I need to hack the DB_query_builder class?

/Mattias
Reply
#2

You can always do anything almost using a standard query with ci even create tables etc;

PHP Code:
$sql "CREATE INDEX index_name ON table_name (column_name)";
$sql "CREATE UNIQUE INDEX index_name ON table_name (column_name)";

$query $this->db->query($sql); 

Using this method you can do almost anything with the database.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

(05-28-2016, 02:53 AM)InsiteFX Wrote: You can always do anything almost using a standard query with ci even create tables etc;

PHP Code:
$sql "CREATE INDEX index_name ON table_name (column_name)";
$sql "CREATE UNIQUE INDEX index_name ON table_name (column_name)";

$query $this->db->query($sql); 

Using this method you can do almost anything with the database.

True, but for sake of getting proper syntax and avoid injection etc, etc we prefer the Query Builder functions.
I ended up patching DB_query_builder with a new function public function from_index($from, $index)
Simply inserting "USE INDEX($index)" after the table name.
Ugly but gets the job done - will probably bite us when we upgrade CI to next version....

Thanks,
/Mattias
Reply
#4

You should not alter any core files (as you mention you will get in trouble with the next update of CI), instead you should extend the class.

https://github.com/bcit-ci/CodeIgniter/w...se-drivers
Reply
#5

(05-29-2016, 12:02 AM)Diederik Wrote: You should not alter any core files (as you mention you will get in trouble with the next update of CI), instead you should extend the class.

https://github.com/bcit-ci/CodeIgniter/w...se-drivers

Great link! I am mostly stuck in the 2.x era in my projects but this will for sure help me with migration.
Reply
#6

(05-29-2016, 12:02 AM)Diederik Wrote: You should not alter any core files (as you mention you will get in trouble with the next update of CI), instead you should extend the class.

https://github.com/bcit-ci/CodeIgniter/w...se-drivers

Didn't get that one going, a couple of errors(? - who am I to judge?!?) in the instructions noted
I am on 3.0.6 and I needed to put MY_Loader into application/core (and not in application/Library).
The command
$this->_ci_assign_to_models();

Doesn't work and I can't find any such command at all in CI.

Anyhow, it is DB_query_builder.php I need to override with my special "from_index()" function and that one is loaded from deep inside DB.php without any option to load my own version.

For the moment, I'll stick with patching DB_query_builder.php directly and hope that unit-testing will catch the problem after upgrading CI.
Reply
#7

Something like "use index" should be extremely rare in your queries, because it indicates that somehow you've found a situation in which your database engine is doing it wrong. In most cases, you would be better off optimizing your query or adding another index.

Also, I found a rather old article (2006) which mentions this situation (migrating to InnoDB and having noticeably slower count() queries) and points out that this should only apply to count() queries without a where clause. A little more digging turned up a slightly more recent article (2009) which more or less points people in the direction you seem to have taken.

Generally, not specifying a where clause would also be a common reason for a query optimizer to not use an index for your query (especially in combination with not specifying the indexed columns in the select clause).

Another item I found was a StackOverflow answer which mentioned creating an additional non-primary index on the primary key column for tables with potentially large fields (like text columns) when InnoDB uses clustered primary keys. ( http://stackoverflow.com/questions/51182...ere-clause ). I even managed to find a (still-older) post defining the row size which is likely to cause a secondary index to be faster than the default index on the primary key: > 450 bytes ( http://optimmysql.blogspot.com/2007/07/i...dexes.html ).
Reply
#8

(06-02-2016, 08:38 AM)mwhitney Wrote: Something like "use index" should be extremely rare in your queries, because it indicates that somehow you've found a situation in which your database engine is doing it wrong. In most cases, you would be better off optimizing your query or adding another index.
That's how I found it - my $this->db->count_all() queries were taking way longer than before after I converted my DB from MYISAM to InnoDB leading me to write my own queries. Since I didn't like to hard-code them I needed a way to specify an index in the $this->db->from() clause.

The articles you are referring to - I don't know if my use-case of InnoDB is extreme, the DB is around 9 GB and the table I got most problems with have around 3M rows. SELECT COUNT(*) FROM table in MYISAM is a snap but after convering to InnoDB it takes around 9 seconds to execute that query unless specifying the PRIMARY index.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB