0

I'm running queries on a single table.

example

SELECT firstName, COUNT(*) as num_bob
FROM DataTable
WHERE firstName= "bob"

A query similar to the one above takes a long time. The database/table does not use indexing and all the data is stored in a single table. Is there a way to rewrite this query to improve performance?

Thanks!

  • create an index on the `FirstName` column. it would help!! – M.Ali Jun 26 '16 at 00:19
  • "database/table does not use indexing" - Why not? – Phil Blackburn Jun 26 '16 at 00:31
  • I didn't create the table and was informed it didn't use indexing. I don't have permission to create an index in the table. That's why I asked if it was possible to create a query that has a better performance without indexing. I don't really understand why my question is being downvoted. –  Jun 26 '16 at 00:43
  • 1
    I agree about down-voting for no reason posted, how can people get better, or fix their posts without a why... As for the database you are stuck with, whoever designed without indexes, IMO is a bonehead and should be reviewed for slow queries, what queries are being used and how indexes can help in all areas. – DRapp Jun 26 '16 at 01:25
  • If you aren't allowed to add any indexes, warm up your resume!! – Rick James Jun 30 '16 at 02:38
  • Why return first name at all and remove the group by? if you know it's bob why do you need it in the result set? Aside from that the best way is indexes as others suggested. – xQbert Jul 02 '16 at 16:03
  • Think about a phonebook and your task is to count the number of listing in the city. It's obviously going to be a lot faster if you don't have to read every line on every page when somebody asks how many Garfunkel's there are. – shawnt00 Jul 02 '16 at 16:13

3 Answers3

0

Try removing GROUP BY...

SELECT firstName, COUNT(*) as num_bob
FROM DataTable
WHERE firstName="bob";

It should give you the same results.

WMRamadan
  • 974
  • 1
  • 11
  • 25
0

You can try

"create table as ( your select query " ) and than select this table

This is faster than normal select.

user6513847
  • 37
  • 1
  • 6
0

First of all if Group By is not required please remove it. If required use DISTINCT and ORDER BY instead of GROUP BY. See this

You may check your ISOLATION LEVEL. If it is under Serializable then no worries. But if it is then stopping other quires to Datable will increase your performance.

Also See this. You are OK for this I think.

Community
  • 1
  • 1
Esty
  • 1,882
  • 3
  • 17
  • 36