0

I have to perform advanced search queries on large databases each with millions of entries.

Rather than hardcoding queries, we wanted to come up with queries on the fly. The advanced search allows you to search depending on various parameters - dates, id's from other columns, including multiple order_id's , order_item's being given in the search field at once. In fact, bulk orders search are it's main focus.

Thus the way the query is framed is the core of how good this app will be.

I am looking for suggestions on what I should be looking out for, or best practices for optimized queries. For eg-:

In the where condition, a column with better index, should be searched first, and then another parameter.Basically select * from orders where order_id in (a,b,c....) and relayed=true; is much better than select * from orders where relayed=true and order_id in (a,b,c....);. As relayed has an index percentage of only 50. It can either be true or false, so the resultant query set is still large

Also as there are loads of in entries, is this a good idea - http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/

Come up with generic examples to illustrate your point relating to select, join, where, order_by. How should queries on such large tables be structured?

I apologize for the general nature of this question, but frankly thought it could be helpful.

Pratik Bothra
  • 2,642
  • 2
  • 30
  • 44
  • 1
    But... MySQL has its own optimizer. It does all this for you. – Tom van der Woerdt Mar 08 '13 at 10:28
  • @TomvanderWoerdt - If that is the case, I would be delighted. But there is no way it does. http://stackoverflow.com/questions/141278/subqueries-vs-joins – Pratik Bothra Mar 08 '13 at 10:31
  • 2
    That depends on the version of MySQL. Tht post was from 2008 so quite dated, although I would still use a join against a sub query rather than using an IN. The order of the WHERE clauses shouldn't really matter I would hope. Beyond that you need to do your joins to exclude the most records first (possibly forcing the issue using a STRAIGHT_JOIN) – Kickstart Mar 08 '13 at 10:37
  • why this question tagged with mysqli? – Your Common Sense Mar 08 '13 at 10:38
  • @YourCommonSense - Now, was it? ;) – Pratik Bothra Mar 08 '13 at 10:42

1 Answers1

0

To perform advanced search queries on large databases each with millions of entries

and Optimizing query on the fly for best results it is good idea to use

http://sphinxsearch.com/

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345