I have a table with about 500 000 rows and need to execute a query with first a WHERE then a GROUP BY and then ORDER BY all on different columns is their any way to optimize this?
The table can look something like this:
row_id INT PRIMARY KEY
where_column SET('a','b','c')
groupby_column VARCHAR(255)
orderby_column DATE
.. more rows that I need to fetch with the select.I have one index on each of the rows.
The query can look like this:
SELECT * FROM my_table WHERE FIND_IN_SET('a',where_column) GROUP BY groupby_column ORDER BY orderby_column DESC,row_id
This is what I have tried but not seen any dramaticly speed improvements with:
I have tried but I can't get mysql to use one index only.
A also get that mysql uses temp tables and also store them disk. I have raised tmp_table_size without any success.
I have experimented with sort_buffer_size, read_rnd_buffer_size, key_buffer_size.
First only fetching the row_id (PRIMARY KEY) and then fetch the rows. This is a bit faster.
Thanks for your help.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
