I agree with Cade Roux.
This article should get you on the right track:
One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column.
Basically it helps your data insert at the end of the index and not cause lots of disk IO and Page splits.
Secondly, if you are creating other indexes on your data and they are constructed cleverly they will be reused.
e.g. imagine you search a table on three columns
state, county, zip.
- you sometimes search by state only.
- you sometimes search by state and county.
- you frequently search by state, county, zip.
Then an index with state, county, zip. will be used in all three of these searches.
If you search by zip alone quite a lot then the above index will not be used (by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.
You could then create an index on Zip alone that would be used in this instance.
By the way We can take advantage of the fact that with Multi-Column indexing the first index column is always usable for searching and when you search only by 'state' it is efficient but yet not as efficient as Single-Column index on 'state'
I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.
The article will help a lot. :-)
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);
Best Answer
Data retrieval -- SELECT statements.
Data manipulation -- INSERT, UPDATE, DELETE statements.
If you feel you want to get better data retrieval performance.
On tables that will see heavy data manipulation -- insertion, updating...
Queries need to address the order of columns when dealing with a covering index (an index on more than one column), from left to right in index column definition. The column order in the statement doesn't matter, only that of columns 1, 2 and 3 - a statement needs have a reference to column 1 before the index can be used. If there's only a reference to column 2 or 3, the covering index for 1/2/3 could not be used.
In MySQL, only one index can be used per SELECT/statement in the query (subqueries/etc are seen as a separate statement). And there's a limit to the amount of space per table that MySQL allows. Additionally, running a function on an indexed column renders the index useless - IE: