"group" mysql results for better performance of php

Question

I have a table:

ID, TYPE, TITLE
1, book, One
2, music, Tear
3, book, My flow
4, music, Yes
5, flower, Green

SELECT * FROM table WHERE TYPE IN ( book, music );

But now I need somehow to group results and to be able to do this for each TYPE in php - where I say in php echo all "book" TYPEs it should echo "One, My flow". The problem is that my table contains 100 000s of lines and result as well. So I need properly "pre-formatted" results for php because it must be fast with many rows. Any idea please?

I don't get the bit about 'pre-formatting'. Generally, issues of data display are best handled in application level code, e.g. PHP, i.e. 'post-formatted' — Strawberry, Jun 03 '15 at 06:36
the best you can do is sort by 'type', and then in your application loop, detect when 'type' changes, and output a new header. — pala_, Jun 03 '15 at 06:56
Thank you guys! It seems this is a way how should I do it (sort and then detect). — peter, Jun 03 '15 at 12:38

score 0 · Answer 1 · answered Jun 03 '15 at 06:38

0

Try this.

SELECT *, GROUP_CONCAT(TITLE SEPARATOR ', ') as titles FROM table WHERE TYPE IN ( book, music ) group by TYPE

answered Jun 03 '15 at 06:38

Umair Khan

1,684
18
34

with hundreds of thousands of rows, it's incredibly likely this will overflow `group_concat`s default max length – pala_ Jun 03 '15 at 06:56
http://stackoverflow.com/questions/5445048/is-there-a-length-limit-to-group-concat-or-another-reason-why-it-would-not-work – Umair Khan Jun 03 '15 at 07:11
http://pentahointegra.blogspot.com/2013/01/how-to-increase-groupconcat-function.html – Umair Khan Jun 03 '15 at 07:11
thank you @Mike DaVinci! Because there is a max length I will try solution with sorting and detecting first. – peter Jun 03 '15 at 12:39
You can increase max length. I had posted two links about it. – Umair Khan Jun 04 '15 at 03:20

"group" mysql results for better performance of php

1 Answers1