Ability to specify index for query build from |
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
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)"; 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 )
(05-28-2016, 02:53 AM)InsiteFX Wrote: You can always do anything almost using a standard query with ci even create tables etc; 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
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 (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. Great link! I am mostly stuck in the 2.x era in my projects but this will for sure help me with migration. (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. 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.
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 ). (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. |
Welcome Guest, Not a member yet? Register Sign In |