How to optimize my MySQL Select query?

Question

I have got a MySQL database table, that has the following form:

CREATE TABLE IF NOT EXISTS data_packet (
  id BIGINT NOT NULL AUTO_INCREMENT,
  time_received BIGINT NOT NULL,
  content TEXT NOT NULL,
  recording_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_data_packet_recording_idx (recording_id ASC),
  CONSTRAINT fk_data_packet_recording
    FOREIGN KEY (recording_id)
    REFERENCES recording(id)
)

Inside this table I have got data like this:

<Dat url="vehicleSpeed">
   <Abs name="speed" val="97"/>
   <Enm name="unit" val="kmh"/>
   <Enm name="state" val="valid"/>
</Dat>

The table can contain up to 1.000.000 rows. Now I have got queries like the one following, to select special data packets with a certain url. Every data packet has such a url attribute.

SELECT * 
FROM data_packet 
WHERE recording_id = 1 
    AND content LIKE '%vehicleSpeed%' 
    AND time_received BETWEEN 1435843095338 AND 1435843095996 
ORDER BY time_received ASC;

I think this constellation can be optimized. In MySQL workbench this query takes 47ms where the table only contains about 35.000 rows. A Java application shall execute the queries later and I noticed, that it takes more time to execute it via the JDBC.

What optimization can you recommend? Index? Another column? Another table?

Thank you very much.

What do you think of making an extra column, where I can store the url separate? What effect do indices have in detail? — tellob, Jul 10 '15 at 07:24
You can make `Unique` constraint on table for the column `id`. As you have already done it like NOT NULL AUTO INCREMENT then you can apply this constraint to improve the performance. — ChiranjeeviIT, Jul 10 '15 at 07:27

score 0 · Answer 1 · answered Jul 10 '15 at 07:25

0

Any query optimization thumb rule says you might want to have an INDEX in same sequence you are putting columns in where clause. I would suggest to create an INDEX with two columns (content,time_received) and then check performance with EXPLAIN command in mySQL.

ALTER TABLE data_packet 
  ADD  INDEX `IDX_COMPOSITE` (content,time_received);

EXPLAIN SELECT * 
FROM data_packet 
WHERE recording_id = 1 
    AND content LIKE '%vehicleSpeed%' 
    AND time_received BETWEEN 1435843095338 AND 1435843095996 
ORDER BY time_received ASC;

Also try without sorting

EXPLAIN SELECT * 
FROM data_packet 
WHERE recording_id = 1 
    AND content LIKE '%vehicleSpeed%' 
    AND time_received BETWEEN 1435843095338 AND 1435843095996

Thanks Anant

answered Jul 10 '15 at 07:25

Anant Laxmikant Bobde

564
2
11

I tried to alter the table and got the following error: Error Code: 1170. BLOB/TEXT column 'content' used in key specification without a key length – tellob Jul 10 '15 at 07:37
Have a look for a very nice explanation http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length – Anant Laxmikant Bobde Jul 10 '15 at 08:25
does it make sense to make 3 indices for: time_received, content, time_received + content? (maybe content is then replaced by an extra column for only storing the url) – tellob Jul 10 '15 at 08:28
You are ANDing content and time_received so would suggest you to not create separate INDEXES and create a single one as suggested instead – Anant Laxmikant Bobde Jul 10 '15 at 09:04
Could you please mark this answer as right and applied if the same helped you – Anant Laxmikant Bobde Jul 10 '15 at 09:05
I have to test, whether it helped me. Wait some time ;) Why not making separate indices? – tellob Jul 10 '15 at 09:06
Its not creating more indices its about their utilization. That's the reason not to create too many indices – Anant Laxmikant Bobde Jul 10 '15 at 09:07
MySQL will only use one index on a table in a query. Hence if you had an index on content and an index on time received, the query would only use one of them, rather than both combined. – Kickstart Jul 10 '15 at 11:19
and what if i i had an index on content, index and content and index? – tellob Jul 10 '15 at 11:45
Still for time_received the whole table will be searched – Anant Laxmikant Bobde Jul 10 '15 at 12:07
An index on content is almost useless as you are using LIKE with a leading wildcard. Hence you would be best storing the url that you are checking in a separate column – Kickstart Jul 10 '15 at 12:30

score 0 · Answer 2 · answered Jul 10 '15 at 11:26

You have an index on recording_id, but you do not have one that also covers time_received. Depending on the number of values of recording_id and the time range they cover this could make a major difference.

Add an index that covers both recording_id AND time_received.

The 2nd major issue is that your query uses LIKE, comparing with a value which has a leading wildcard. No index in going to help with this. You could try using a FULLTEXT index, and then using MATCH()...AGAINST to try and find the matching values.

However I would suggest that it might be better to restructure your database. But this very much depends on the fragments of data that are stored in the content field. If all of them contain the url field then I would possibly just store that in the content field (so you can check it directly without the need for if), and then store each of the other fields in another table which you can join to the data_packet table.

How to optimize my MySQL Select query?

2 Answers2