x

MySQL query performance

Hi All, We have a MySQL db with a query which looks up data through joins from huge tables (more than 6 million records). I get disctinct counts or do substring_index() multiple times to parse a column string and retrieve the required text. then I use this data to populate flash based fusion charts in a grails-groovy application.

Right now the complex graphs are getting timed out, simple ones which access smaller tables load properly, so How do I Improve query performance for the complex graph. We are trying to parse the data before storing it into the table so we dont need to do substring_index on columns, and we are having indexes on the primary keys and that long string column. Here is one sample query for a graph:

    Select count(m.cnt) as
dist_object_cnt,m.employeeid from 
(SELECT
distinct(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
`transactiontext` , '<-->', 2
),'<-->',-1),'OBJECTNAME=',-1)) as
cnt, 
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
`transactiontext` , '<-->', 3
),'<-->',-1),'OBJECTTYPE=',-1) AS
OBJECTTYPE, 
U.EMPLOYEEID,U.FIRSTNAME,U.LASTNAME,
U.TITLE,U.MANAGER,
DATE_FORMAT(UT.DATE, '%b %d, %Y') FROM
USER_TRANSACTION UT, USERS U,
TRANSACTION T, RESOURCE R WHERE  DATE
BETWEEN '2011-08-01' AND '2011-08-20'
AND U.ID = UT.USER_ID AND UT.TRANS_ID
= T.ID AND T.TRANSACTIONTEXT LIKE '%<-->%' AND R.ID = UT.RESOURCE_ID AND
R.RESOURCENAME = 'Agile'  ORDER BY
u.lastname desc, u.firstname asc) m
group by m.employeeid order by
dist_object_cnt desc limit 100;
more ▼

asked Oct 27 '11 at 03:15 PM in Default

priyank15 gravatar image

priyank15
342 9 9 10

This forum is about SQL server and Oracle, but maybe you'll get some help anyway.
Oct 28 '11 at 12:28 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You mention that you have an index on the long string column, do you mean the TRANSACTIONTEXT column? In that case, it's not used anyway because of your LIKE '%<-->%' search. When you use the % wildcard in the beginning of your search string the condition is not searchable and a scan is used instead.

I am using SQL server and from my point of view I would look at the following:

  • Are you getting some kind of "cross join" between your tables?
  • Do you have index on Resourcename and date?
  • Do you use covering indexes (all columns in your select, join and where clause in the same index) or single column indexes? What's your clustered index? If your indexes are not covering, you'll get an index seek an multiple clustering index lookups (one for each match in index
seek, look for nested loops).
more ▼

answered Oct 28 '11 at 12:44 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x362
x242
x66

asked: Oct 27 '11 at 03:15 PM

Seen: 920 times

Last Updated: Oct 28 '11 at 12:33 AM