Mysql – Picking Fields in a Multiple-Field Index on MySQL

mysqlsql

I have this MySQL query that I'd like to optimize:

SELECT min(`topic_id`) FROM `gallery_topics` where `topic_id` > 11 AND `topic_poster` = 5 AND `topic_status` = 0

I wanted to add an index with multiple fields to optimize this query, so I added a key I named previous_by_author with these three keys in this order:

topic_id
topic_poster
topic_status

I did an EXPLAIN of this query, with the expectation that the EXPLAIN query would tell me that the query uses my new previous_by_author index. However, EXPLAIN reported that the query uses the user_start_time index, which uses the following two fields: 1. topic_poster and 2. topic_start_time.

I don't get it, topic_start_time is a datimetime field that isn't even in my query!

Here's the full result of the EXPLAIN query:

id  select_type  table           type   possible_keys      
1   SIMPLE       gallery_topics ref     PRIMARY,user_start_time,previous_by_author

(rows continued)

key              key_len  ref   rows   Extra
user_start_time  8        const 4      Using where

What fields should I use in my multiple-field index so that my query can take advantage of the best index?

Best Solution

Without knowing what all available indexes are, it's difficult to say why one in particular was chosen. But it's easier to see why your new index wasn't chosen: since you have a greater-than condition on topic_id, and the index stores values sorted first by topic_id, MySQL will have to read in (at least from the index) all rows with topic_id > 11 and check the additional conditions on the WHERE clause. Better to put topic_id last in the multicolumn index, so MySQL will group values in the index by the two equality conditions; then, checking the greater-than condition is easy.

Also, how many rows are in the table? If you don't have representative test data, all bets are off on what the query planner will decide to do.

Summary: for optimizing queries exactly like the one you show, try the index

topic_status
topic_poster
topic_id

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Best Solution

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Mysql – Should I use the datetime or timestamp data type in MySQL

Related Question