Explain select is this okay?

jemz · November 19, 2014, 1:46pm

Hi I tried to use the EXPLAIN function in mysql to test if my join queries is faster and this is the result.

I am having difficulties to understand the result.I hope you can help me so that I can improve my query.

Thank you in advance.

r937 · November 19, 2014, 4:47pm

we’re not likely to be able to offer much in the way of analysis without seeing the table definitions and the query too

also, here’s your EXPLAIN laid out in a more readable fashion than a CSV

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE dl ref device_id device_id 22 const 231769 Using index condition; Using temporary; Using filesort 1 SIMPLE rfinfo ALL device_id \N \N \N 4 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ud ALL user_id \N \N \N 14 Using where; Using join buffer (Block Nested Loop)

jemz · November 20, 2014, 6:51pm

@r937 ,

Here is my table structure.

CREATE TABLE `user_devices` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `device_id` varchar(20) NOT NULL,
  `dateTime_added` datetime NOT NULL,
  `device_type` int(11) NOT NULL,
  `device_name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=52 DEFAULT CHARSET=latin1


CREATE TABLE `rfidinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` varchar(20) NOT NULL DEFAULT '0',
  `rfid` varchar(15) NOT NULL DEFAULT '0',
  `datepunch` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8


CREATE TABLE `device_locations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `device_id` varchar(20) NOT NULL,
  `latitude` varchar(15) NOT NULL,
  `longitude` varchar(15) NOT NULL,
  `dateTime_created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`)
) ENGINE=MyISAM AUTO_INCREMENT=750632 DEFAULT CHARSET=latin1

This is how I query.,
First I get all the devices_id which is belong to the $loginid.

Select device_id from user_devices where user_id = '$loginid'

Then I create a while loop in-order to get each record base on device_id from user_devices

                select dl.col1,
                          dl.col2,
                          dl.col3,
                          dl.col4,
                          dl.col5,
                          rfinfo.rfcol1,
                          rfinfo.rfcol2,
                          ud.col1
                  from user_devices ud 
                       inner join device_locations dl
                     on ud.device_id = dl.device_id
                     left outer join rfidinfo rfinfo
                     on  dl.device_id =rfinfo.device_id
                  where dl.device_id = '$from_user_deviceid' AND ud.user_id = '$loginid'
                  order by dl.dateTime_created desc LIMIT 0,1

Thank you in advance.

r937 · November 20, 2014, 7:19pm

a while loop? you’re running that query once for each device?

that is ~definitely~ inefficient

jemz · November 20, 2014, 11:56pm

@r937,

Thank you for the quick reply,can you please help me what should I do in order to make it efficient witout using the while loop.

Thank you in advance.

r937 · November 21, 2014, 5:35pm

SELECT dl.col1 , dl.col2 , dl.col3 , dl.col4 , dl.col5 , dl.dateTime_created AS latest , rfinfo.rfcol1 , rfinfo.rfcol2 , ud.col1 FROM user_devices AS ud INNER JOIN ( SELECT device_id , MAX(dateTime_created) AS latest FROM device_locations GROUP BY device_id ) AS dx ON dx.device_id = ud.device_id INNER JOIN device_locations AS dl ON dl.device_id = dx.device_id AND dl.dateTime_created = dx.latest LEFT OUTER JOIN rfidinfo AS rfinfo ON rfinfo.device_id = ud.device_id WHERE ud.user_id = '$loginid'

jemz · November 22, 2014, 3:26am

@r937,

Thank you for the quick reply…wow I never seen this kind of query before.

I just want to ask is this normal,when I run it to mysql editor it gives this result, it takes 10.094 sec.

/* Affected rows: 0  Found rows: 9  Warnings: 0  Duration for 1 query: 10.094 sec. */

Thank you in advance.

r937 · November 22, 2014, 11:47am

it’s normal if you don’t have the appropriate indexes on your tables

you have only the index on user_id in the first table

add these and try your query again –ALTER TABLE user_devices ADD INDEX (device_id) ; ALTER TABLE rfidinfo ADD INDEX (device_id) ; ALTER TABLE device_locations ADD INDEX (device_id, dateTime_created) ;

jemz · November 22, 2014, 4:09pm

@r937 ,

wow This is the result after putting this index,very very fast

/* Affected rows: 0 Found rows: 9 Warnings: 0 Duration for 1 query: 0.000 sec. */

I just want to ask some question what is the difference of adding this way of index

in my device_locations table.after I issue the command show create table device_locations

KEY `device_id` (`device_id`,`dateTime_created`) //this is faster

other way of adding index

KEY device_id (device_id),
KEY dateTime_created (dateTime_created)

/* Affected rows: 0 Found rows: 9 Warnings: 0 Duration for 1 query: 1.078 sec. */

Thank you in advance

r937 · November 22, 2014, 7:17pm

i can explain it real simple, if you would be willing to look at some data

jemz · November 23, 2014, 1:08pm

@r937,

Yes,please I am willing.

r937 · November 23, 2014, 5:00pm

okay, here we go, there will be four cases of data to look at

this is an exercise to show you the power of indexing, and why un-indexed tables are so slow to read

here’s what i want you to do – using only inspection, i.e. just by looking at the data, please tell me the MAX(dateTime_created) for each of the three device_id values 111, 777, 1234

for the first example, the data has no indexes at all –

device_id dateTime_created 777 2014-04-21 1234 2014-08-14 777 2014-04-17 1234 2014-06-23 777 2014-02-19 111 2013-12-09 111 2014-09-13 777 2014-03-17 777 2014-02-18 777 2014-04-13 1234 2014-05-19 777 2014-05-18 111 2014-08-17 111 2014-07-14 777 2014-03-16 777 2014-01-22 1234 2014-04-23 777 2014-08-11 111 2013-11-13 777 2014-02-17 111 2014-08-09 111 2014-02-24 111 2014-10-28 1234 2014-11-19 777 2013-12-22 1234 2013-10-24 777 2014-07-16 1234 2014-08-18 1234 2014-03-15 777 2014-03-18 1234 2013-11-19 1234 2014-07-23 1234 2014-04-10 1234 2014-05-21 777 2014-03-22 111 2014-11-23 777 2014-01-17 1234 2014-07-19 1234 2014-10-24 111 2014-03-22 1234 2014-09-09 111 2014-10-09 1234 2014-06-16 777 2014-04-22 111 2014-09-12 777 2014-05-17

for the second example, again just by looking at the data, find me the MIN(dateTime_created) for each device_id

this time, the data has an index on device_id

device_id dateTime_created 111 2013-12-14 111 2014-09-17 111 2014-11-28 111 2014-03-27 111 2014-08-22 111 2014-08-14 111 2014-09-18 111 2014-11-02 111 2014-03-01 111 2014-07-19 111 2014-10-14 111 2013-11-18 777 2014-04-27 777 2013-12-27 777 2014-08-16 777 2014-04-22 777 2014-03-27 777 2014-02-24 777 2014-03-23 777 2014-01-22 777 2014-02-22 777 2014-03-21 777 2014-01-27 777 2014-04-26 777 2014-02-23 777 2014-05-23 777 2014-07-21 777 2014-04-18 777 2014-03-22 777 2014-05-22 1234 2014-03-20 1234 2014-06-28 1234 2014-05-24 1234 2014-07-28 1234 2014-08-23 1234 2014-09-14 1234 2014-05-26 1234 2013-10-29 1234 2013-11-24 1234 2014-04-15 1234 2014-10-29 1234 2014-07-24 1234 2014-11-24 1234 2014-08-19 1234 2014-06-21 1234 2014-04-28

there are two more examples to go, but first i want to see your answers, and any comments you had about how hard it was for you

jemz · November 23, 2014, 11:18pm

@r937,
maximum date,here it takes time because the device_id are not group,and I need to to go top and down down to top just to look the device_id with max date.

111     2014-11-23

777     2014-07-16 

1234     2014-11-19

minimum date,here it’s easy for me to see find the device_id.because the device_id are in there group.but the date is not arranged.

111 2013-11-18

777 2013-12-27

1234 2013-10-29

r937 · November 24, 2014, 12:13am

in example 1, the maximum date for 777 was 2014-08-11

okay, here’s example 3, this time there is an index on dateTime_created

what is the MAX(dateTime_created) for each device_id?

device_id dateTime_created 1234 2013-11-11 111 2013-12-01 1234 2013-12-07 111 2013-12-27 777 2014-01-09 777 2014-02-04 777 2014-02-09 777 2014-03-07 777 2014-03-08 777 2014-03-09 111 2014-03-14 1234 2014-04-02 777 2014-04-03 777 2014-04-04 777 2014-04-05 111 2014-04-09 777 2014-04-09 1234 2014-04-28 777 2014-05-01 777 2014-05-05 777 2014-05-09 777 2014-05-10 1234 2014-05-11 777 2014-06-04 777 2014-06-05 1234 2014-06-06 1234 2014-06-08 1234 2014-07-04 1234 2014-07-11 111 2014-08-01 777 2014-08-03 1234 2014-08-06 1234 2014-08-10 111 2014-08-27 777 2014-08-29 1234 2014-09-01 111 2014-09-04 1234 2014-09-05 1234 2014-09-27 111 2014-09-30 111 2014-10-01 111 2014-10-27 1234 2014-11-11 111 2014-11-15 1234 2014-12-07 111 2014-12-11this example is misleading because you can ~easily~ see the answers by “looking ahead” which of course the database cannot do

jemz · November 24, 2014, 1:06pm

@r937,

in response to the example 3,

1234     2014-12-07 


111     2014-12-11


777     2014-08-29

yes it easy to find the maxdate because the date are arranged…but the device_id are not.

r937 · November 24, 2014, 2:17pm

[quote=“jemz, post:15, topic:104530, full:true”]
yes it easy to find the maxdate because the date are arranged…but the device_id are not.
[/quote]but you had to read the entire table to find them, which was the result of the index on dateTime_created only – and you had the benefit of “looking ahead” or in this case backwards from the end

last example, index on ( device_id , dateTime_created )

find the MIN(dateTime_created) for each device_id

device_id dateTime_created 111 2013-12-01 111 2013-12-27 111 2014-03-14 111 2014-04-09 111 2014-08-01 111 2014-08-27 111 2014-09-04 111 2014-09-30 111 2014-10-01 111 2014-10-27 111 2014-11-15 111 2014-12-11 777 2014-01-09 777 2014-02-04 777 2014-02-09 777 2014-03-07 777 2014-03-08 777 2014-03-09 777 2014-04-03 777 2014-04-04 777 2014-04-05 777 2014-04-09 777 2014-05-01 777 2014-05-05 777 2014-05-09 777 2014-05-10 777 2014-06-04 777 2014-06-05 777 2014-08-03 777 2014-08-29 1234 2013-11-11 1234 2013-12-07 1234 2014-04-02 1234 2014-04-28 1234 2014-05-11 1234 2014-06-06 1234 2014-06-08 1234 2014-07-04 1234 2014-07-11 1234 2014-08-06 1234 2014-08-10 1234 2014-09-01 1234 2014-09-05 1234 2014-09-27 1234 2014-11-11 1234 2014-12-07

jemz · November 24, 2014, 2:23pm

@r937,

Ah, I see now so this the benefit if we do index like this,very quick!.

 index on ( device_id , dateTime_created )



111     2013-12-01

777     2014-01-09

1234     2013-11-11

jemz · November 24, 2014, 2:42pm

@r937,

Thank you so much

system · February 23, 2015, 9:52pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.