CODEWITHSUNDEEP
mysqlperformance
david
david

Reputation: 3360

does text variable effect queries speed ? mysql

I have table that contain 2 long text columns , when I fetch 100 rows it takes 5 seconds, this is long time right ? maybe this happen because I have this 2 long text columns ?

here it is the table structure :

CREATE TABLE `tempBusiness2` (
  `bussId` int(11) NOT NULL AUTO_INCREMENT,
  `nameHe` varchar(200) COLLATE utf8_bin NOT NULL,
  `nameAr` varchar(200) COLLATE utf8_bin NOT NULL,
  `nameEn` varchar(200) COLLATE utf8_bin NOT NULL,
  `addressHe` varchar(200) COLLATE utf8_bin NOT NULL,
  `addressAr` varchar(200) COLLATE utf8_bin NOT NULL,
  `addressEn` varchar(200) COLLATE utf8_bin NOT NULL,
  `x` varchar(200) COLLATE utf8_bin NOT NULL,
  `y` varchar(200) COLLATE utf8_bin NOT NULL,
  `categoryId` int(11) NOT NULL,
  `subcategoryId` int(11) NOT NULL,
  `cityId` int(11) NOT NULL,
  `cityName` varchar(200) COLLATE utf8_bin NOT NULL,
  `phone` varchar(200) COLLATE utf8_bin NOT NULL,
  `userDetails` text COLLATE utf8_bin NOT NULL,
  `selectedIDFace` tinyint(4) NOT NULL,
  `alluserDetails` longtext COLLATE utf8_bin NOT NULL,
  `details` varchar(500) COLLATE utf8_bin NOT NULL,
  `picture` varchar(200) COLLATE utf8_bin NOT NULL,
  `imageUrl` varchar(200) COLLATE utf8_bin NOT NULL,
  `fax` varchar(200) COLLATE utf8_bin NOT NULL,
  `email` varchar(200) COLLATE utf8_bin NOT NULL,
  `facebook` varchar(200) COLLATE utf8_bin NOT NULL,
  `trash` tinyint(4) NOT NULL,
  `subCategories` varchar(500) COLLATE utf8_bin NOT NULL,
  `openHours` varchar(500) COLLATE utf8_bin NOT NULL,
  `lastCheckedDuplications` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `bussStatus` tinyint(4) NOT NULL,
  `approveStatus` tinyint(4) NOT NULL,
  `steps` tinyint(4) NOT NULL DEFAULT '0',
  `allDetails` longtext COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`bussId`),
  KEY `bussId` (`allDetails`(5),`bussId`),
  KEY `face` (`alluserDetails`(5),`userDetails`(5),`bussId`)
) ENGINE=InnoDB AUTO_INCREMENT=2515926 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

my query = SELECT * FROM tempBusiness2 LIMIT 100

Upvotes: 0

Views: 48

Answers (2)

Rick James
Rick James

Reputation: 142208

If SELECT * FROM tempBusiness2 LIMIT 100 is really your query, then no INDEX is involved, and no INDEX would make it run any faster.

What that statement does:

  1. Start at the beginning of the "data". (In InnoDB the PRIMARY KEY and the data are 'clustered' together. So, you are coincidentally starting with the first value of the PK.)
  2. Read that row.
  3. Move to the next row -- This is easy and efficient because the PK & Data are stored in a B+Tree structure.
  4. Repeat until finished with the 100 or the table.

But... Because of lots of TEXT and VARCHAR fields, it is not that efficient. No more than 8K of a row is stored in the B+Tree mentioned above; the rest is sitting in extra blocks that are linked to. (I do not know how many extra blocks, but I fear it is more than one.) Each extra block is another disk hit.

Now, let's try to "count the disk hits". If you run this query a second time (and have a reasonably large innodb_buffer_pool_size), there would be any disk hits. Instead, let's focus on a "cold cache" and count the data blocks that are touched.

If there is only one row per block (as derived from the 8KB comment), that's 100 blocks to read. Plus the extra blocks -- hundred(s) more.

Ordinary disks can handle 100 reads per second. So that is a total of several seconds -- possibly the 5 that you experienced!.

Now... What can be done?

Don't do SELECT * unless you really want all the columns. By avoiding some of the bulky column, you can avoid some of the disk hits.

innodb_buffer_pool_size should be about 70% of available RAM.

"Vertical partitioning" may help. This is where you split off some columns into a 'parallel' table. This is handy if some subset of the columns are really a chunk of related stuff, and especially handy if it is "optional" in some sense. The JOIN to "put the data back together" is likely to be no worse than what you are experiencing now.

Do you really need (200) in all those fields?

You have what looks like a 3-element array of names and addresses. That might be better as another table with up to 3 rows per bussId.

On another note: If you run EXPLAIN SELECT ... on all your queries, you will probably find that the "prefix indexes" are never used:

KEY `bussId` (`allDetails`(5),`bussId`),
KEY `face` (`alluserDetails`(5),`userDetails`(5),`bussId`)

What were you hoping for in them? Consider FULLTEXT index(es), instead.

Why do you have both city_id and city_name in this table? It sounds like normalization gone berserk.

Upvotes: 1

ber2008
ber2008

Reputation: 323

Yes, this kind of column take a lot of time, return this column only when you need. And in addiction you will have to do a index on your table.

Upvotes: 0

Related Questions