Performance Tuning for InnoDB?

MySQL & MariaDB Other MySQL® Questions
1

Dear Forum-Users,

first: Sorry for my poor english :slight_smile: I’ll give my best that you all understand my questions :slight_smile:

I have a debian 5.0 System with percona 5.5 as deb-package.
Before i had mysql 5.5. i have switched to the percona server, because i have a big database. this contains a table with 5 mio. rows in innoDB. When i do a select with sort and while on this table, it take about 5-10 seconds to show me the result.

For the innoDB buffer pool size i have granted 17.000 MB.
The data-files of the sqlserver are on a fast SSD-Drive.

What can i do to get a faster query? The indexes are correct.

Thank you for help :slight_smile:

Angelo

2

You will need to be more specific. Include the exact query, the time it takes to run, SHOW CREATE TABLE, and EXPLAIN.

3

An example Query is:
SELECT id, von, an, betreff, datum, flags, priority, size, color FROM bm60_mails WHERE userid = ‘3’ AND folder=‘0’;

It takes in phpMyAdmin 1,21 seconds.

But it can take sometimes up to 10 seconds.

An Explain gives:
1 SIMPLE bm60_mails index_merge oquery,mailUser,mailFolder mailUser,oquery 4,4 NULL 4487 Using intersect(mailUser,oquery); Using where

What do you mean with SHOW CREATE TABLE?