MySQL indexing for multiple count(*) queries

I'm lost regarding indexing. I have a moderately complex web app that I'm building for a client and it has several count(*) queries that are all running very slow (0.3 seconds)

Here's a q开发者_开发百科uick example

SELECT COUNT( * ) AS  `count` 
FROM  `vehicles` 
WHERE  `VehicleLocation_province` =  'Alberta'
AND  `default_image_URI` IS NOT NULL 
AND  `default_image_URI` !=  ''

Here's the explain..

 1 SIMPLE vehicles ref VehicleLocation_province,VehicleLocation_province_...     VehicleLocation_province 2 const 14128 Using where

I can't even get this query to use a proper index, never mind some of the more complex queries such as

SELECT * , ( 6371 * ACOS( COS( RADIANS( 53.543564 ) ) * COS( RADIANS( lat ) ) * COS( RADIANS( lng ) - RADIANS( - 113.490456 ) ) + SIN( RADIANS( 53.543564 ) ) * SIN( RADIANS( lat ) ) ) ) AS  `distance` 
FROM  `vehicles` 
WHERE  `Make` =  'Pontiac'
AND  `BodyStyle` =  'Sedan'
AND  `VehiclePrice` >=  '1'
AND  `VehiclePrice` <=  '36000'
AND  `VehiclePrice` IS NOT NULL 
AND  `default_image_URI` IS NOT NULL 
AND  `default_image_URI` !=  ''
HAVING `distance` < 50
ORDER BY `VehicleReceivedDate` DESC LIMIT 25

Explain

1 SIMPLE vehicles ref Make,BodyStyle,VehicleLocation_province_2 Make 99 const 5821 Using where; Using filesort

I know I need to avoid temp tables and filesorts where possible... but how is this practically accomplished when several of these count(*) queries must be executed on each request with varying where parameters grouping and ordering?

Well, the only way to get indexes speed up queries is to have indexes cover enough conditions that the selectivity becomes useful (or for RDBMS to be able to use indexes to calculate aggregates such as count).

Do not fail to realize that having index on (Make) and index on (BodyStyle) is not the same as having index on (Make, BodyStyle).

In your first query, when you need to count the records, the existence of index that covers default_image_URI and VehicleLocation_province should be enough for the mysql not to do a table scan, but retrieve the count from the index.

You can check this by creating an index (VehicleLocation_province, default_image_URI) and then running query and/or examining explain.

In the second query you have similar situation with query that has more conditions (which is good as long as they are all AND conditions) and it is not about counting records, but actually retrieving the data from the table and sorting.

Few notes here:

• notice your conditions IS NOT NULL and !='' - if these conditions are commonly present in your queries then these suggest that your design is not proper, and that you have denormalised different entities into one table so now you have to sort them out each time you want to use the data (this is just an indication, and I assume that you apply these conditions a lot, which might not be true)
• having said that, if you look at 2nd query and if Make and BodyStyle are covered with a composite index and have low selectivity the query will still perform fast
• mysql has to choose one index to access the data and it will try to choose the index that returns least number of rows given statistics and available conditions (so that further conditions have to loop through least number of records) - if that index helps only with reducing the resultset the sorting will be done using a filesort. For index to be useful for both selecting rows and sorting it should have useful order or indexed columns, for example in the above query index on (Make, BodyStyle, VehicleReceivedDate) might do the trick
• adding proper indexes on your table should help, but indexes can not fix problems with design

Another option which I hadn't considered (which I now view as the ideal solution) is the use a search server like Sphinx to handle the heavy lifting when if comes to indexing and performance. Sphinx can do grouping and counting as well as fulltext and attribute searching/filtering on many columns in several different search configurations.

Sphinx can even do @geodist for radius calculations with SetGeoAnchor ( $attrlat, $attrlong, $lat, $long )

It makes little sense, when running complex search queries to attempt to re-invent the wheel and end up with ridiculous lists of multi-column indexes attempting to cover all manner of use cases.

I wish I'd thought of a search server earlier in the project-- now, it's a little late to save face on performance issues.

http://sphinxsearch.com