MySQL Forums
Forum List  »  Performance

Re: Attempt to split big table into smaller one made query slower
Posted by: Rick James
Date: March 15, 2010 07:35PM

<=> -- sorry for the notation. This is a typical mapping table:
CREATE TABLE map (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   PRIMARY KEY(id),
   UNIQUE(name)
);
That lets you map between id and name. The id is probably much smaller than name, so using the id in the other table(s) saves space.

Compound indexes... Suppose there is a directory of persons, and you want to look up a P. St.Onge. An index on first name is virtually useless. An index on lastname would get to all the St.Onges in the directory. An index on (lastname, firstname) would be more efficient -- WHERE lastname = 'St.Onge' AND firstname LIKE 'P%'.

MySQL won't (usually) use two indexes in a single query.

This:
and g.clientID = 110
and g.dateAdded >= '2010-03-01 07:00:01'
and g.dateAdded <= '2010-03-01 07:34:15'
can use a compound index on (clientID, dateAdded) by first locating the 110, then scanning over the date range. If, instead, you had (dateAdded, clientID) or just (dateAdded), all it could do is scan over the date range, stumbling over all the various clientIDs. Think of looking up P. St.Onge given (firstname, lastname) -- it would look for all the firstnames beginning with P, and have to skip over lots of lastnames.

Since you are JOINing two tables, it will first reach into one of the tables, then for each row that it finds there (after filtering out via the WHERE clause), reach into the other table. I can't be sure which table it will start with, so I designed enough indexes so it could efficiently start by reaching into either table, and to efficiently do the secondary reach into the other table.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.