how do I perform this sql search..

Dear Yiiers,

could you please hint me how to implement a sql like below with Yii,

e.g.

SELECT COUNT(*) as total FROM TABLENAME force index(MYINDEX1) WHERE uid = 123 AND categoryid=345 ORDER BY modified_time DESC LIMIT 1

Thanks!




$criteria=new CDbCriteria;

$criteria->select= "SELECT COUNT(*) as total";

$criteria->condition="uid = 123 AND categoryid=345";

$criteria->orderby="modified_time";


TABLENAME::model()->find($criteria);



I don’t know how to implement this “force index(MYINDEX1)”, anyway you can (very badly indeed) workaroud with:


$criteria->join ="force index(MYINDEX1)";

"up"

I have used the "bad" workaround. I had to hint mySQL to use the right index - it kept selecting the wrong one. After that, huge reduction of slow queries and hunger for CPU.

I would like to see a better method in Yii.

Actually I don’t think it’s the best way to deal with these things from your application.

Seems like it’s time to optimize and probably restructure your table structure.

It’s the DB’s job to think about queries, so if it does not use the right index (query plan is bad) - something’s wrong and must be fixed.

So OPTIMIZE TABLE, CHECK TABLE and EXPLAIN ANALYZE are your friends now.

Hi

I agree that as yourself, I expect the database engine to do the job.

I assure you that I have turned this up and down doing optimize, check and mainly explain analyze with manual adjustments of the query (including simplifying it, removing priority implied by the parenthesis and changing the order of priority implied by it), and adding indexes (with different orders of the fields, etc).

Here is the last slow query with profile information before I added the ‘USE INDEX’ hint.


# Time: 140402 16:38:54

# User@Host: root[root] @ localhost []  Id: 295284

# Schema: locbox  Last_errno: 0  Killed: 0

# Query_time: 1.014998  Lock_time: 0.000150  Rows_sent: 1  Rows_examined: 711374  Rows_affected: 0

# Bytes_sent: 1367

# Profile_starting: 0.000072 Profile_starting_cpu: 0.000072 Profile_checking_permissions: 0.000003 Profile_checking_permissions_cpu: 0.000002 Profile_checking_permissions: 0.000002 Profile_checking_permissions_cpu: 0.000002 Profile_Opening_tables: 0.000031 Profile_Opening_tables_cpu: 0.000032 Profile_init: 0.000039 Profile_init_cpu: 0.000039 Profile_System_lock: 0.000006 Profile_System_lock_cpu: 0.000007 Profile_optimizing: 0.000020 Profile_optimizing_cpu: 0.000020 Profile_statistics: 0.000352 Profile_statistics_cpu: 0.000353 Profile_preparing: 0.000053 Profile_preparing_cpu: 0.000053 Profile_Sorting_result: 0.000003 Profile_Sorting_result_cpu: 0.000002 Profile_executing: 0.000002 Profile_executing_cpu: 0.000002 Profile_Sending_data: 1.014020 Profile_Sending_data_cpu: 1.011638 Profile_end: 0.000007 Profile_end_cpu: 0.000006 Profile_query_end: 0.000008 Profile_query_end_cpu: 0.000008 Profile_closing_tables: 0.000015 Profile_closing_tables_cpu: 0.000015 Profile_freeing_items: 0.000360 Profile_freeing_items_cpu: 0.000031 Profile_logging_slow_query: 0.000003 Profile_logging_slow_query_cpu: 0.000003

# Profile_total: 1.014996 Profile_total_cpu: 1.012284

SET timestamp=1396449534;

SELECT `t`.`live_position_id` AS `t0_c0`, `t`.`device_id` AS `t0_c1`, `t`.`lat` AS `t0_c2`, `t`.`lon` AS `t0_c3`, `t`.`odometer` AS `t0_c4`, `t`.`time` AS `t0_c5`, `t`.`date_add` AS `t0_c6`, `t`.`log_reason` AS `t0_c7`, `t`.`valid` AS `t0_c8`, `t`.`speed` AS `t0_c9`, `t`.`pdop` AS `t0_c10`, `t`.`nsat_used` AS `t0_c11`, `t`.`nsat_view` AS `t0_c12`, `t`.`extra_info` AS `t0_c13`, `t`.`address` AS `t0_c14`, `t`.`consumed` AS `t0_c15`, `t`.`is_good_position` AS `t0_c16`, `t`.`state` AS `t0_c17` FROM `live_positions` `t`  INNER JOIN `devices` `device` ON (`t`.`device_id`=`device`.`device_id`)  WHERE (((`t`.`time`<'2014-04-02 14:38:34') AND (`t`.`is_good_position`=1 OR `t`.`is_good_position` IS NULL AND  (`t`.`lat` IS NOT NULL AND `t`.`lon` IS NOT NULL AND `t`.`lat` <> 0 AND `t`.`lon` <> 0 AND ( (`t`.`pdop` IS NULL) OR (`t`.`pdop` < 3.5) OR ((`t`.`pdop` < 6) AND `device`.`device_type_id` in (6,5,<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />))))) AND (`t`.`device_id`='1089')) ORDER BY `t`.`time` DESC LIMIT 1;

I have tried (I have not checked the parenthesis here):


(((`t`.`time`<'2014-04-02 14:38:34') AND (`t`.`is_good_position`=1))) AND (`t`.`device_id`='1089')) ORDER BY `t`.`time` DESC LIMIT 1;

[size="2"]and [/size]


(( (`t`.`is_good_position`=1))) AND (`t`.`device_id`='1089')) ORDER BY `t`.`time` DESC LIMIT 1;

(and removing the inner join);

Basically what happens is that mysql selects the index for the ‘time’ field which appears in the ORDER BY part (even when I remove the time condition in the where clause).

"Forcing" mysql to use the index (device_id,is_good_position,time) fixed it. No more slow queries, the number of mysql threads drop from an average of 8 to an average of 5, and load average dropped by a bit more than 50%.

Any suggestion to avoid the ‘USE INDEX’ is welcome.

Wow, that’s interesting. Usually the problem is quite the opposite: mysql prefers restricting index over that in ‘order by’.

So as I see, you have two indices: one for time and the other one is composite (device_id,is_good_position,time)

Maybe you can reverse it, that is (time,device_id,is_good_position). That’s crazy but who knows. MySQL is not good at composites.

Initially I had (time,device_id,is_good_position), initially thinking that mysql would prefer to sort first, then select the device_id and then the position.

However, the analysis showed that mysql systematically selected the records based on device_id first ("const") and then used the time index (not the composite).

I could certainly push the analysis a bit further (checking which index is fastest (device_id,good_position) or (device_id,time) or the three columns). I intend to get rid of the complex condition in case ‘is_good_position’ is null, but this was not the limiting factor for speed. And as advice goes, and given the effort needed, here I only optimize that what is impacting performance. [By default, I optimize as much as I can through architecture and “as I code”].

I’m afraid the best optimization here is to use PostgreSQL :)