3 ms·

Ignore the constraints of the form A>10. Only consider the constraints of the form C=3. If many of your queries are of the form C=3 and many are also of the for

by xirium 18y ago

Ignore the constraints of the form A>10. Only consider the constraints of the form C=3. If many of your queries are of the form C=3 and many are also of the form C=3 AND D=4 and some are also of the form C=3 AND D=4 AND E=5 then create a compound index of the form (C,D,E) One index of this form will reduce a some of the full table scans to more specific range scans. Create permutations and variations of this type index to cover all common cases. Next, what storage engines are you using? Do your queries have any ORDER BY clauses?

ca98am79 18y ago
xirium - thanks very much for your help. Unfortunately, most of the constraints are in the form of A>10, etc... I am using MyISAM now, but I am going to give MEMORY a try after some of the comments on this post. There are no ORDER BY clauses.
ca98am79 18y ago
So I loaded it into memory, but still SELECTs which are true for 99% of the table (10 million+ rows) are taking 1 min 7.95 sec, which is way too long. I guess the best way to do this is just to create many tables which are subsets of the data for queries that return large amounts - and then come up with an algorithm which selects from these subsets based on the query? For example, if a>1 returns 99% of the data - create and store a table with this selection and if someone selects where a>1 AND b>5, query the subset. I don't know. If I do that, I will end up with GBs and GBs of tables. I wish there were some magic algorithm to make things 10x faster.
- xirium 18y ago
  If you're using MyISAM and you don't care about diskspace then you can use an R-tree index to search any two inequalities. For example, an R-tree index can be used for queries of the form A>10 AND A<100 AND B>5. Also note that R-tree indexes can be appended to my previously suggested indexes. So, an index of the form (C,D,E,R) would efficiently locate rows for a query of the form C=3 AND D=4 AND E=5 AND A>10 AND A<100 AND B>5

by xirium 18y ago

ca98am79 18y ago

xirium - thanks very much for your help. Unfortunately, most of the constraints are in the form of A>10, etc... I am using MyISAM now, but I am going to give MEMORY a try after some of the comments on this post. There are no ORDER BY clauses.

ca98am79 18y ago

So I loaded it into memory, but still SELECTs which are true for 99% of the table (10 million+ rows) are taking 1 min 7.95 sec, which is way too long. I guess the best way to do this is just to create many tables which are subsets of the data for queries that return large amounts - and then come up with an algorithm which selects from these subsets based on the query? For example, if a>1 returns 99% of the data - create and store a table with this selection and if someone selects where a>1 AND b>5, query the subset. I don't know. If I do that, I will end up with GBs and GBs of tables. I wish there were some magic algorithm to make things 10x faster.

xirium 18y ago
If you're using MyISAM and you don't care about diskspace then you can use an R-tree index to search any two inequalities. For example, an R-tree index can be used for queries of the form A>10 AND A<100 AND B>5. Also note that R-tree indexes can be appended to my previously suggested indexes. So, an index of the form (C,D,E,R) would efficiently locate rows for a query of the form C=3 AND D=4 AND E=5 AND A>10 AND A<100 AND B>5