There has to be a better way? - DB Queries |
[eluser]Devon Lambert[/eluser]
I have an annoying DB query problem that is hurting the performance on my site and on the pages that make use of these functions/controllers. Does anyone know of a better way for me to produce the queries found in the model below? i.e. call it all in 1 function? produce a more efficient query? Use some CI magic? For the time being, while I am working/debugging I have enabled the CI cache for this controller but I don't particularly like that as a production solution as I want the effect provided by presenting my users with random query calls. Each time these this page is loaded it takes about 4-5 seconds on my xampp server. That could be a nightmare on a production server with actual traffic. Here's the code: MODEL: Code: function get_special() { CONTROLLER: Code: // Create the special_items array VIEW: Code: <div>
[eluser]oddman[/eluser]
Firstly, I'd look at the database schema and see if it can be improved, if it can't, move onto step 2. 2. Indexes. Whenever you use a field in a where clause, it should be indexed. If not, you're asking for trouble.
[eluser]Unknown[/eluser]
Check all queries with explain (http://dev.mysql.com/doc/refman/5.0/en/explain.html).
[eluser]gtech[/eluser]
also set db_debug to FALSE in config/database.php you will be surprised how much things get speeded up. [edit] maybe my understanding was incorrect, it seemed to speed up my automated testing quite significantly but that was on an old version I will do some performance tests when I have time.[/edit]
[eluser]Devon Lambert[/eluser]
Thanks all. Trying all three implementations this afternoon. I'll post results.
[eluser]Devon Lambert[/eluser]
Sorry I didn't get back sooner to report the results, but I'm still trying the "index" fix. Turning DB debug off did not speed up the query process, or should I say, it did not speed it up enough for me to notice. I ran a few SQL queries using EXPLAIN and realized that one of the queries could make use of an index but it still seemed to run fairly slow (6 - 7 seconds) for the query to pull. When I have some more free time I will examine the DB further and see if there is another column that can benefit from an index. Will also re-evaluate the DB schema. I suppose now is better than the future to weed out issue in the DB. Thanks again for the help all. |
Welcome Guest, Not a member yet? Register Sign In |